Conditional Formating Interaction within Several Ranges on Excel

[Asender]

Hi there.

I have a spreadsheet which looks roughly like this:

Available Location Trigger1 Trigger2 Trigger3 Trigger4
    Y       File1    MEM1                       MEM1
    Y       File2    MEM2     MEM2              MEM2
    Y       File3    MEM3                       MEM3
    N       File4    MEM4              MEM4	
    Y       File5    MEM5              MEM5	
    N       File6    MEM6              MEM6	
    Y       File7    MEM7			
    N       File8    MEM8     MEM8

The functionality I am aiming for is:

First: When column 'Available' is changed to value 'Y' then that cell changes from a red background to a green background.

Next: Any 'Trigger' prefixed column that has a cell which is populated on the same row as the 'Available' cell with a value of 'Y' will change its background from red to green also.

Finally: If all populated cells in a 'Trigger' prefixed column now have a green background, the header cell will the appropriate Trigger name will change background from red to green.

The general purpose of the sheet is to highlight to the user, who is updating the availability of files, when a particular Trigger job can be run.

I apologise for asking for a complete solution but as a mainframe SAS programmer who has hacked with VB in the past this has me beat.

Thanks for your help,



A.

 

[GlennUK]

Set your default colour of red background first. Then apply conditional formatting to change cell backgrounds to green where appropriate. I'll assume that you know how to do that for the 'Available' column.

For the data under the Trigger columns, select the data area, and set a condition of:

=AND(C2<>"",$A2="Y")

And for the Trigger titles, select them, and do a condition of:

=COUNTIF(C2:C9,"<>")=SUMPRODUCT(--($A2:$A9="Y")*--(C2:C9<>""))

Cheers, Glenn.

Beauty is in the eye of the beerholder.