Coloring Cells based on multiple values/other Cells that contain Dates

[rhybeka]

One of my managers asked me to do this for him, and I've been trying most of the day to wrap my brain around a 'simple' solution to this and coming up dry. I need to have the cells in column G color based on a value in themselves or in adjacent cell in column H.

I have three columns (F,G,H) all with dates in them. Column F is manually filled in, column G is calculated with a workday function

(=IFERROR(WORKDAY($F6,25,Holidays2016)," ")

to be 25 days later and column H is also manually filled in.
[TABLE="width: 500"]
<tbody>[TR]
[TD](F)
Date of Meeting
[/TD]
[TD](G)
Report Due Date
[/TD]
[TD](H)
Report Published
[/TD]
[/TR]
[TR]
[TD]1/7
[/TD]
[TD]2/11
[/TD]
[TD]1/14
[/TD]
[/TR]
[TR]
[TD]1/7
[/TD]
[TD]2/11
[/TD]
[TD]1/14
[/TD]
[/TR]
[TR]
[TD]1/8
[/TD]
[TD]2/12
[/TD]
[TD]1/21
[/TD]
[/TR]
[TR]
[TD]1/13
[/TD]
[TD]2/17
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The background color needs to be in cell/column G but is also dependent upon the adjacent cell values in column H.

-if cell G is blank, than color clear/white

- if there is no date in column H and the date in column G is within 7 days, color it yellow

-if the date in cell G is today, make it red

I can't seem to figure out how to write a function that will allow conditional formatting to make this work. I was thinking maybe adding an extra column to do some of the calculation would be better but can't figure out how to do that and then have it color cell G.

I might be able to use the color function VBA and just code the macro to run on open so the user doesn't have to click a button or anything. Working through that thought process now.


Any input would be greatly appreciated. Thanks!

Beka

 

[FDibbins]

If you are familiar with CF, ignore the 1st 2 points
1. highlight the range you want to apply the conditional formatting to (say, G2:G100)
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =G2="".................Format Fill clear/white

Repeat 3 and 4 with =AND(H2="",G2+7<=X)...(put here what your criteria date is, I didn't see where you mentioned that?)
Format Fill YELLOW
New Rule =G2=TODAY().........format fill RED

 

[rhybeka]

Thanks Ford! It seems to be getting closer but not quite there yet. as of what I have with the below, I have no colors in the G column. I must have something set incorrectly or don't understand CF as well as I thought I did!

I have the =TODAY function is in S2.
I have =S2-7 in the S3 cell

The dates start in F/G/H cell 6 and goes through cell 30

So if there is no date in the H cell, color G cells white/blank
Formula: =H6=""

If the date in column G is seven days or less from today's date, make it yellow
Formula: =AND(H6="",G6>=S3)

If the date in column G is today, make red
Formula: =G6=S2

 

[FDibbins]

I have =S2-7 in the S3 cell

I think this needs to be + not -

 

[rhybeka]

I think this needs to be + not -

The formula in S2 is TODAY. So S3 is TODAY - 7(days) so the cell would be yellow seven days before 'today' - right or no?

 

[FDibbins]

Yes, that would be correct. You dont need an extra cell for TODAY()-7, although it does no harm to have it

If the date in column G is seven days or less from today's date, make it yellow
Formula: =AND(H6="",G6>=S3)

If it is less than today-7, shouldnt that be =AND(H6="",G6<S3) ?

 

[rhybeka]

Yes, that would be correct. You dont need an extra cell for TODAY()-7, although it does no harm to have it


If it is less than today-7, shouldnt that be =AND(H6="",G6<s3) ?[="" quote]



instead of =AND(H6="",G6>=S3) ? I don't think so. There's nothing to tell it to be seven days less? I tried it nevertheless and I still get nothing. I have a few dates I updated to make column G today and nothing is coloring - no red or yellow. So I'm going to take another stab at this.
</s3)>