Conditional Formatting one cell based on another cell

[ERINWILLIS01]

HELP! I have been trying to get this figured out for hours now I would like column D to turn red if it past the due date, but not if the invoice has been paid which shows in Column P. So for example, row 25, 34 and 35 have all been paid (column P), but Column D "Due Date" is still showing red as if it's past due. Any help is tremendously appreciated!

 

[Joe4]

Conditional Formatting formula to use (for row 25, in this example):

=AND($D25>0,$D25<TODAY(),$P25="")

 

[ERINWILLIS01]

Conditional Formatting formula to use (for row 25, in this example):

=AND($D25>0,$D25<TODAY(),$P25="")

How can I do this for the whole range of cells? Like I need D3-D39 to turn red when past due, but only if there is a paid date in the corresponding P Column. I put the formula in that you provided, but it didn't work.

 

[Joe4]

You can apply this to a whole range of cells at once, provided you do it correctly. There are two keys:
1. Select the entire range of cells that you want to apply this to (D3:D9)
2. Write the formula as it applies to the very first cell in the range you have selected above. Excel will automatically adjust for all the other rows.
So the formula should look like:

=AND($D3>0,$D3<TODAY(),$P3="")

 

[ERINWILLIS01]

That didn't work for me. I need to do the whole range from D3 through D39

 

[Joe4]

That didn't work for me. I need to do the whole range from D3 through D39

Stop, slow down, and VERY carefully re-read what I told you in my last post (I think you overlooked some key details).
Do EXACTLY as I instruct. Please do not reply back until you have actually tried it out.
Also be sure to select your desired formatting color after you enter the formula.

If it doesn't not work, then I question whether or not column P is really blank, or if there is a space in there.

 

[ERINWILLIS01]

Ok, so I went into each cell of the P column that does not have a date entered and made sure there was no text, no spaces, nothing in there. I copied your formula directly from your response (copy&paste) so I know it is correct. I took a screenshot. Yes I hit the done button and only have it opened to manage so you can see I put the formula in there.

 

[Joe4]

Did you remember to remove all other existing rules?

Are you really using Excel 365, or something else. like Google Sheets?
I ask because this is what Excel 365 looks like for me, when setting up that formula (which looks different than what you posted):

I created a small example of data, and set-up the rule exactly as I explained, and it seems to be working properly, as the only record that should be highlighted is highlighted:

All your dates are entered as valid dates, and not text, right?

 

[Joe4]

If all the date entries are indeed dates (and not text), and you are still having issues getting it to work, please run this VBA code on that sheet.
It will remove ALL existing Conditional Formatting Rules and create the one I show above.

Sub MyCFMacro()

'   Remove all current Conditional Formatting rules
    Cells.FormatConditions.Delete
    
'   Apply new rule to cells D3:D39
    With Range("D3:D39")
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND($D3>0,$D3<TODAY(),$P3="""")"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13408767
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
    
End Sub

See if that works.

 

[ERINWILLIS01]

If all the date entries are indeed dates (and not text), and you are still having issues getting it to work, please run this VBA code on that sheet.
It will remove ALL existing Conditional Formatting Rules and create the one I show above.

Sub MyCFMacro()

'   Remove all current Conditional Formatting rules
    Cells.FormatConditions.Delete
   
'   Apply new rule to cells D3:D39
    With Range("D3:D39")
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND($D3>0,$D3<TODAY(),$P3="""")"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13408767
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
   
End Sub

See if that works.

YES! I just went in and did this! I had to change the formula settings to include formulas referring to empty cells. I think I finally got it! Thank you so much for bearing with me and helping solve this!