How do I make excel change the colour of a cell depending on a different cells date?

debsenheimer 

To make the following work, enter dates in the month cells in row 1, formatted as mmm to display the abbreviated month name.

For example, in L1: 10/1/24, in M1 11/1/24 etc.

Select L2:X12 or further to the right and down.

L2 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'less than or equal to' from the second drop down.
In the box next to it, enter the formula

=AND($H2<>"", EOMONTH(L$1, 0)>$H2)

Click Format...
Activate the Fill tab.
Select black as color.
Click OK, then click OK again.

debsenheimer 

Select L1:X100 (or further if you like).

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'equal to' from the second drop down.
In the box next to it, enter the formula

="N/A"

Click Format...
Activate the Fill tab.
Select black as fill color.
Click OK, then click OK again.

HansVogelaar 

I have a spreadsheet with quarterly invoices. I want all cells in a row to turn black after the date in column H (ie where the N/As are in the first 5 rows.) and I want my range to be columns L,M,N,Q,R,S, U,V,W. (I will have different numbers of rows depending on the sheet and the year adn would rather not have to specify range.) I'm trying to tell it to look at a cell, check the header row to determine if that date is after the date in Column H, and if so change all other cells in date columns black for the rest of the row. And I want it repeated for the whole sheet. I just can't figure out how to code that. 

This is what I want it to look like at the end.

Is there some form of conditional formatting or macro use that I could make this happen?

Thanks!

wjwhittle , you are welcome

SergeiBaklan Thank you! works perfectly

wjwhittle 

For the blue you may use conditional formatting rule with formula

=COUNTIF($H:$H,B1)

applied to $B$1:$F$50.

Another similar rule is for red.

When I insert a column - it seems to inherit the formatting - so its AutoMagic !
I keep the most current stats on the left of the sheet next to the legend - the Old data moves out to the right ... 

MrKasifs 

That means you may apply the rule to the range $B$2:$XFD$1000000 if you don't care about performance, but it's better to take more realistic gap.

MrKasifs 

In previous post

"Tomorrow I will insert the column for the October 9th"

That means you are expanding the range, isn't it? Or you always have exactly the same number of columns?

MrKasifs 

You may expand you range to the right as necessary (here is to AZ column) and additionally check if next cell is not empty

Rule iterates all cells within the range. Since we use relative references, B2 is compared with C2, C2 with D2, D2 with E2, etc. to the right and down.

You may check in attached file.

MrKasifs 

In first case since the range you applied the rule starts from C2, condition also shall start from it, i.e. 

Cell value > C2

Cell value < C2

MrKasifs 

You set up the rule incorrectly - as you can see in your first screenshot, the rules apply to C2:K19.

Since they refer to A2, the rules should have applied to B2:K19 (or even further to the right)...

HansVogelaar

No, the requirements have not changed - you will observe that the values in the yellow boxed sections are incorrectly highlighted - because 
D2 is < E2 = Green not Red
J2 < K2 
G6 < H6 

So I need to set up the conditional formatting for Every Column relative to the previous column. 
BUT At least I can use the Format painter to apply it. 

 Thank you, 

HansVogelaar 

I had a feeling it was not a simple answer.  I greatly appreciate the help.  I will try out the macro in the Visual Basic Editor.

Scottsgt739 

Excel does not have a built-in way to do this. This will require VBA code.

Copy the following macro into a module in the Visual Basic Editor:

Sub ColorCells()
    Const shn1 = "Sheet1" ' Name of first sheet
    Const shn2 = "Sheet2" ' Name of second sheet
    Const col1 = "A"      ' Column on first sheet
    Const col2 = "A"      ' Column on second sheet
    Dim wsh1 As Worksheet ' First sheet
    Dim wsh2 As Worksheet ' Second sheet
    Dim rng1 As Range     ' Cell on first sheet
    Dim rng2 As Range     ' Cell on second sheet
    Application.ScreenUpdating = False
    Set wsh1 = Worksheets(shn1)
    Set wsh2 = Worksheets(shn2)
    wsh2.Columns(col2).Interior.ColorIndex = xlColorIndexNone
    For Each rng2 In Intersect(wsh2.Columns(col2), wsh2.UsedRange)
        Set rng1 = wsh1.Columns(col1).Find(What:=rng2.Value, LookAt:=xlWhole)
        If Not rng1 Is Nothing Then
            If rng1.Interior.ColorIndex <> xlColorIndexNone Then
                rng2.Interior.Color = rng1.Interior.Color
            End If
        End If
    Next rng2
    Application.ScreenUpdating = True
End Sub

Change the constants at the beginning of the macro as needed.

Run the macro whenever you want to update the colors on the second sheet.

Scottsgt739 

Have the names on Sheet1 been colored manually or by a conditional formatting rule?

MrKasifs 

If your requirements have changed, please explain them again in detail, preferably with an example.

MrKasifs 

Yes - once again, that is exactly what the rule that I proposed does.

The formula =A2 applies to cell B2. In this formula, both the column reference A and the row reference 2 are relative. This means that Excel will automatically adjust them for other rows and columns.

So in B3, Excel will use =A3, in B4, it will use =A4 etc.

And  in C3, Excel will use =B3, in D3, it will use =C3 etc.

If I had used =$A$2, the column and row references would have been absolute, i.e. fixed.