Conditional Formatting using date range and additional cell value

[Wez]

Greetings from Down Under,

Long time reader, first time poster.

I am trying to create a conditional format that highlights a range of cells based on a date range and the letter value in another cell. I need the cells to shade a certain colour depending on the letter value of the other cell

I have managed to conditionally format the cells based on date using

=IF((G27<$F$27)*(G27>$E$27), TRUE, FALSE

Where
E27 = First date of range
F27 = last date of range
G27 to Z27 = cells containing calendar dates to be highlighted

but cannot get the cells to change colour based on an additional letter value. Not sure if I can have an AND statement attached to the above

I need it to basically perform the following:

If B27 equals "XX" AND =IF((G27<$F$27)*(G27>$E$27), TRUE, FALSE then highlight the date cells red.

If B27 equals "YY" AND =IF((G27<$F$27)*(G27>$E$27), TRUE, FALSE then highlight the date cells blue.

Etc.

I realise that each letter value for B27 may require a new conditional format.

I have tried a number of options but cannot get it to work.

I hope that makes sense.

Can anyone help?

Regards

Wez

 

[Wez]

Forgot to mention I am using Excel 2007

 

[Cindy Ellis]

Hello, and welcome to the forum!
I'm by no means a conditional formatting expert, but the following formula seems to work:

=--(G1>$E$1)*--(G1<$F$1)*--(OR($B$1="XX",$B$1="YY"))

Additional optional conditions can be appended to the OR part of the function using the same structure as the "YY" part.
The "--" parts convert each section from TRUE/FALSE to 1/0 respectively, so that the multiplication doesn't blow up.

Hope that helps,
Cindy

 

[Wez]

Thanks Cindy, works great,

I have applied it as such:

as two separate conditions

=(G28>$E$28)*(G28<$F$28)*(OR($B$28="XX")) .... format Green
=(G28>$E$28)*(G28<$F$28)*(OR($B$28="YY")) .... format Red

Now is there a way to apply it to multiple rows? Or do I need to have the the conditions set for each row?

 

[Cindy Ellis]

If you remove the $ before the "27" for all parts that are row dependent, you can then copy row 27, and use "Paste Special - Formats" to copy to additional rows. Be sure to leave the $ in front of any parts that should be anchored regardless of the data row.
If that's not clear, please post back and I'll try to explain it a different way. I've written it 3 ways so far, and none of them are crystal clear, not knowing if everything in the formula is row-dependent, or only parts of it.
Cindy

 

[T. Valko]

Thanks Cindy, works great,

I have applied it as such:

as two separate conditions

=(G28>$E$28)*(G28<$F$28)*(OR($B$28="XX")) .... format Green
=(G28>$E$28)*(G28<$F$28)*(OR($B$28="YY")) .... format Red

Now is there a way to apply it to multiple rows? Or do I need to have the the conditions set for each row?

You don't need the OR function.

Use AND like this:

=AND(G28>$E$28,G28<$F$28,$B$28="XX")

=AND(G28>$E$28,G28<$F$28,$B$28="YY")

 

[Wez]

Thanks Guys,

Seems so much simpler than how I imagined it.