Conditional format based on a cell's color

2
votes

I have a column with conditional formatting rules set to make the background one of three colors based on how close the date is in comparison to the current date (Today's date + 7, + 15, +30 days).

I want to extended the conditional format across to effect the entire row, rather than just the individual cell for the user more easily to see which item's date is approaching.

Is it possible to do a conditional format using a cell's format?

Update: To explain it further. Conditional format is set to a column: if a cell value is in between today's date and 7 days from today's date, it is formatted to have a green background. I want this format to extend across the ROW in which the value turns green.

2
exceldatetimeexcel-formulaexcel-2010conditional-formatting
Have you tried using the format painter to do this? Have you tried copy and then highlight the cells you want to change and paste formatting only? excel-easy.com/examples/paste-options.html#formatting – Soulfire
I did not want it to be a manual operation. I wanted excel to automatically color the row in. Due to the nature of how the dates are being generated, it isnt a static value which is why im curious to see if this is possible. I dont know the proficiency level of my end user so i kinda wanted to keep it at a KISS level. – user2532255
If the formatting is conditional, it is automatic if the value meets any of the specified criteria. I think I may be confused as to what you are asking though, my apologies. Do you mean you want to programmatically apply conditional formatting to cells automatically? – Soulfire
If it can be done in VBA thats fine, i was trying to avoid that route since it isnt my strongest suit. To rephrase: I have a Cell that is currently being formatted based on its value. I wanted that format to extend along its row automatically. – user2532255
Alright, I think my original comment stands then. You can copy the cell that has the conditional formatting you want (either by right-clicking --> copy or CTRL+C) and then highlight all of the cells that you want the formatting to change then pasting format (as in the link I posted in my original comment). This will copy the conditional formatting to all of the cells you paste format into. I would only recommend VBA if you will be adding/removing rows. – Soulfire

2 Answers

5
votes

Please try:

SO25392253 example

where the three formulae are:

Green =$A1<TODAY()+7
Orange =$A1<TODAY()+15
Yellow =$A1<TODAY()+30

and I have assumed each 'row' spans A:Z and your dates are in ColumnA.

This will highlight dates earlier than today also (though could easily be adjusted).

1
votes

Just change the Applies to range to all the columns you want to be colored. You'll need to make sure you lock down the reference of the date column in the Conditional Formatting rule.

For example:

If your data is in columns A:H, then your Applies to range will be =$A:$H

If your date column is column C, then your conditional formatting rule (for C1) would be something like:

=AND($C1>=TODAY(),$C1<=TODAY()+7)