We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now


Conditional Formatting

GordonMasson asked
Medium Priority
Last Modified: 2012-05-11
I need a conditional formatting formula that will work for a complete column with the following criteria.

Looking at row 5 for example:
If column H is empty then format column G
Format should be…..if the date in column G is before todays date then cell fill  for G5 should be red…if its after todays date cell fill for G5 should be green

(Excel 2007 by the way)
Watch Question

see attached and let me know if that is what youare looking for
Actually this may be more what you are looking for

Formulas are


and =



I need to format complete columns.
Each cell in the column is formatted based on the cell in the previous column in the same row

So say H5 is empty
Assuming G5 is 30 April 11 then it will be formatted green.
If G5 is 1 April 11 then it will be formatted red.

Similarly, if  H10 is empty
Assuming G10 is 30 April 11 then it will be formatted green.
If G10 is 1 April 11 then it will be formatted red.

Same thing goes for all rows in the column if you know what I mean?
how far down the column do you need to go?  


a long way.... probably 600 rows max?

Cant you put conditional formatting on the entire column?
see attached
the last has it on the entire column, applied it to A:J
you can change it to apply to A5:J600 and then it won't do the entire column, but at least you can control where it starts........



Is that not still based on the value in the cell A1 for all rows?

the formatting should produce something that looks like the attached.
using that formula it changes as you move across the headers (which is what i assumed you were doing)......can you post a sample sheet that you want, here is what it looks like with the last proposal, notice the dates go across the top


The sheet i attached above is what i am looking for with regards to formatting although i have done the fill on that manualy.
Each cell in column G will be formated if the cell on the same row in column H is blank
If its not blank then the cell in column G will be either red or green depending on todays date.
this should work then
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview



Almost there but there is still a slight problem.
If i delete the text Sent from cell H14 then G14 goes green as required
However G15 goes red in this case???
I also need this to work for the entire column G based on the values in H even if new values are added in.

Thanks for your help
this happened on the last version that i uploaded?


Sorry i haddent seen the latest version.
Yep that does what i was looking for. Thanks very much for your help.
sorry it took so long to understand exactly what you were looking for, thank you for the grade!



Sorry... one last question.
Looking at the format you used can i just make sure i understand what it means please:


I take it =AND means that all the terms in the bracket are checked so:

H5=""  If H5 is blank  AND ALSO
G5>TODAY()  the date in cell G5 is biggere than todays date AND ALSO
G5>0 the date in G5 is bigger than 0.....not quite sure why this one is included?

THEN Format the cell as shown.
So i think thats all ok but i am not sure about the G5>0 bit

The other thing i dont understand is that if i look at the formatting of say cell G20, it still uses
dont you need to make it =AND(H20="",G20>TODAY(),G20>0)

Thanks Brandon
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.