Tuesday, October 21, 2014

yogi_Conditionally Format Cells A5 To W23 -- Color BLUE If Cell Is Greater Than Or Equal To Z3 And Color RED If Cell Is Also Greater Than Or Equal To AA3

                 Google Spreadsheet   Post  #1802
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct-21-2014
post by  Baheru Mengistu:

I'm having a little trouble using the Conditional Formatting.  I have an array of numbers over several columns, and depending on their value, I'd like the font color to be blue or red.  Specifically, there will be two cells elsewhere in the spreadsheet that will have varying numbers (e.g., 16 in cell X1 and 24 in cell X2, or ).  If each of the numbers in columns A through J are between the values of X1 and X2, they should be blue.  If they're equal to or higher than the value of X2, they should be red.

Because X1 and X2 are not static numbers, I needed to use two Custom Formulas.  Unfortunately, I can only seem to get one of the conditions to apply at any time.

Any help would be greatly appreciated.
Hi Yogi


On the PSS sheet, I need to have the numbers in each cell from A5 to W23 compared to Z3 and AA3.  If each number in the range is equal to or greater than the number in Z3, it should be blue.  However, if it is also equal to or greater than the number in AA3, it should be red instead.

The numbers in Z3 and AA3 are the result of a VLOOKUP from sheet DCL, based upon the input in cell A1.  Essentially, as A1 changes, Z3 and AA3 change, and consequently, so should the colors of the numbers in A5:W23.

I thought that using the following custom formulas over the range of A5:W23 should work:

=A5>=$Z$3 --> Blue font
=A5>=$AA$3 --> Red font

The first one certainly works, but it looks like the second one does not, even when it's the only condition.  I'm not sure why that should be the case.