conditional format of a cloumn

[mart10]

I am using Excel 2010 I have some column headers like

Sun Mon
21-Feb-16 22-Feb-16 etc etc

The Mon text is derived from the date. I am building a template and want to warn users better not to put in data on Sun or Sat columns. The column position will of course vary as and when they use the template because the first date can vary. I tried also sorts of ways to use conditional formatting on Sat or Sun but it just doesn't format the appropriate column

can you help

In fact the ideal solution would be conditional cell locking - now that would be good! :>)

 

[combo]

You can use data validation, with formula referring to the header's weekday. However, users still will be able to paste data.
Alternatives: protect some cells or remove holidays.
If the conditional formatting could be the solution for you, rather refer to background date and test it with WEEKDAY function.

combo

 

[SkipVought]

So under what condition(s) would the cell be locked and unlocked?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mart10]

Column to be locked if the condition in the cell was a Sat or a Sun

Hope this makes sense to those out there

 

[combo]

Assuming your dates (21-Feb-16,...) are real dates in row 1, active cell is in any cell in column B (bigger range can be selected) the custom formula for data validation could be: =WEEKDAY(B$1;2)>5 . Additional info for the user you can put in error message associated with DV. The same formula can be used in conditional formatting to dynamically format cells.


combo

 

[mart10]

Combo - yes by searching on tek tips and using weekday test on conditional formatting this is what I have done and it works, but regarding validation I need to use this to control the input between a range of data -1mill to +1mill (also I don't think you can use your method anyway)

I have working solution BUT ideally would like to put lock control on these columns

 

[combo]

No problem if: 1. you can pack all conditions in one formula for data validation and 2. users need only warning with simple blockade of entering invalid data (they still will be able to paste everything).

combo

 

[SkipVought]

To conditionally lock on cells, you need that controlled by VBA macro event code.

BUT, all users must ENABLE MACROS or the code will not work.

In order to "force" users to ENABLE MACROS, your program must have a Splash sheet (the ONLY visible sheet) warning the user to ENABLE MACROS. If they don't, they don't get to see the hidden sheets that are the main thing. If they do enable macros, then the hidden sheets are made visible and the splash sheet is hidden, making the workbook usable and locking the appropriate cells. When the user saves or closes the workbook, it is put back into the splash and hidden sheets configuration and saved.

You would need to post in forum707 and perform a portion of the VBA coding yourself with aid from members like combo and me. Are you up to this task?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}