CODEWITHSUNDEEP
excelexcel-formulaconditional-formatting
honkin
honkin

Reputation: 113

Conditional Formatting Excel Based On Date In Another Cell

I looked through everything re conditional formatting, but nothing touched on my issue.

I am trying to highlight cells in a column based on the date in another cell, as well as the text in the cell in question. I have been able to do both parts separately as basic conditional formatting, but not sure how to have it all work as one using a formula.

A1 has the text and D1 has the date. If the date is today and the text is either 2 or 3, I want the cell to be coloured using conditional formatting.

Here is the formula I tried for conditional formatting, but with no result:-

=IF(AND($D1=TODAY(), OR(A1="3", A1="2")))

Basically, if the date in D1 is today and A1 is either a 2 or 3, then apply the conditional formatting. Seems simple, but I can only get it working as separate parts

Thanks in advance

Upvotes: 1

Views: 4010

Answers (3)

VBasic2008
VBasic2008

Reputation: 54807

Just 'Imagine' IF in Conditional Formatting

  • Every date in Excel is actually a number, e.g. for 27.01.2019 it is 43492. You can format a cell containing a date, as number, and see for yourself.
  • When there is time with a date, there is additionally a decimal part, e.g. for 27.01.2019 14:52:17 the number would be approximately 43492.6196, which is different than 43492.

Therefore you have to use the INT function to round the number down to the nearest integer resulting in the following conditional formatting formula:

=AND(INT(D1)=TODAY(),OR(A1="FA_Win_3",A1="FA_Win_2"))

used e.g. for cell E1.

  • So the logic behind the formula could be interpreted: (just imagine the IF): IF the rounded down value of cell D1 is equal to TODAY's value (date) AND the value in cell A1 is EITHER FA_Win_3 OR FA_Win_2, DO apply the formatting (, or else don't).

Upvotes: 1

Ricardo Diaz
Ricardo Diaz

Reputation: 5696

I’m not in front a computer, but you can try this:

=AND(TEXT($B2,"dd/mm/yyyy")=TEXT(TODAY(),"dd/mm/yyyy"), OR(A1="FA_Win_3", A1="FA_Win_2"))

If you have trouble with users pasting over the cells and messing up with the conditional format, you can add this soubroutine to the worksheet.

To add it:
- Press F11
- Double click the sheet name
- Copy paste the code
- Read the comments inside the code and adapt it to fit your needs
- Save the workbook as macro-enabled 

Private Sub Worksheet_Change(ByVal Target As Range)

    ' This method has a drawback and is that the undo feature in this sheet no longer works (if you can live with it, no problem)
    ' Here is an option to bring it back: https://www.jkp-ads.com/Articles/UndoWithVBA04.asp

    ' Define variables
    Dim targetRange As Range
    Dim formulaEval As String

    ' Define the Range where they paste the date. This is the range that receives the conditional format
    Set targetRange = Range("B2:B70")

    ' Define the formula evaluated by the conditional format (replace ; for ,)
    formulaEval = "=AND(TEXT(" B2 ",""dd/mm/yyyy"")=TEXT(TODAY(),""dd/mm/yyyy""), OR(A" 2 "=""FA_Win_3"", A" 2 "=""FA_Win_2""))"

    If Not Intersect(Target, targetRange) Is Nothing Then
        With Target
            .FormatConditions.Add Type:=xlExpression, Formula1:=formulaEval
            .FormatConditions(.FormatConditions.Count).SetFirstPriority

            ' This is where the format applied is defined (you can record a macro and replace the code here)
            With .FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = RGB(0, 176, 80)
                .TintAndShade = 0
                .Font.Color = RGB(255, 255, 255)
                .Font.Bold = True
            End With
            .FormatConditions(1).StopIfTrue = False
        End With
    End If

End Sub

Please mark this answer if it helped you

Upvotes: 0

Harun24hr
Harun24hr

Reputation: 36870

Either use 

=IF(AND($D1=TODAY(), OR($A$1=3, $A$1=2)),TRUE,FALSE)

Or

=AND($D1=TODAY(), OR($A$1=3, $A$1=2))

Upvotes: 0

Related Questions