[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

Conditional Formatting

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

Thanks
(Excel 2007 by the way)
0
GordonMasson
Asked:
GordonMasson
  • 11
  • 7
1 Solution
 
KnutsonBMCommented:
see attached and let me know if that is what youare looking for
CondFormat.xlsx
0
 
KnutsonBMCommented:
Actually this may be more what you are looking for

Formulas are

=A1<Today()

and =

A1>Today()
CondFormat.xlsx
0
 
GordonMassonAuthor Commented:
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?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
KnutsonBMCommented:
how far down the column do you need to go?  
0
 
GordonMassonAuthor Commented:
a long way.... probably 600 rows max?

Cant you put conditional formatting on the entire column?
0
 
KnutsonBMCommented:
see attached
CondFormat.xlsx
0
 
KnutsonBMCommented:
the last has it on the entire column, applied it to A:J
0
 
KnutsonBMCommented:
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........

brandon
0
 
GordonMassonAuthor Commented:
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.
Format.xlsx
0
 
KnutsonBMCommented:
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
CondFormat.xlsx
0
 
GordonMassonAuthor Commented:
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.
0
 
KnutsonBMCommented:
this should work then
Copy-of-Format.xlsx
0
 
KnutsonBMCommented:
doh i messed up that last one, this should do the trick for you
Copy-of-Format.xlsx
0
 
GordonMassonAuthor Commented:
Hi

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
0
 
KnutsonBMCommented:
this happened on the last version that i uploaded?
0
 
GordonMassonAuthor Commented:
Sorry i haddent seen the latest version.
Yep that does what i was looking for. Thanks very much for your help.
0
 
KnutsonBMCommented:
sorry it took so long to understand exactly what you were looking for, thank you for the grade!

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

=AND(H5="",G5>TODAY(),G5>0)

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
=AND(H5="",G5>TODAY(),G5>0)
dont you need to make it =AND(H20="",G20>TODAY(),G20>0)

Thanks Brandon
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 11
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now