Conditional format based upon the date string

Question

In my table I have the date in the format "dddd , mmmm dd, yyyy". So for example "Sunday ,July 05,2020". I want the macro to format cells that have the word "Sunday" in and can't get it to work. I can change it to the dd string (ie 17) or the yyyy string (ie 2020) and it works, but not the dddd or mmmm strings which contain text.

Sub colour()
Dim rng As range
Dim lastRow As Long
Dim cell As range
lastRow = Cells(Rows.Count, 3).End(xlUp).Row
Set rng = range("J2:J" & lastRow)
For Each cell In rng
'If I change "Sunday" to "17" for example, or "2020", the routine works, but I cannot get it to find 
'the dddd string
If InStr(cell.Value, "Sunday") > 0 Then
range(cell.Address).Interior.ColorIndex = 19
Else
range(cell.Address).Interior.ColorIndex = 0
End If
Next cell
End Sub

Dates are numbers in Excel, the rest is just formatting. You could try `cell.text` instead of value. — SJR, Jul 03 '20 at 12:44
Eureka! Thank you that worked. I'd like to modify my code to highlight the whole row and would welcome any suggestions? — Stephen Callaghan, Jul 03 '20 at 13:24

score 0 · Answer 1 · answered Jul 03 '20 at 12:59

Stephen,

You can do this directly on the sheet:

Or by code:

Option Explicit

Sub HighlightSundays()

   Dim rng As Range

   Set rng = Range("A1:A21")
   
      With rng
          .FormatConditions.Add Type:=xlExpression, _
           Formula1:="=WEEKDAY(A1)=7"
          .FormatConditions(.FormatConditions.Count).SetFirstPriority

        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
           .TintAndShade = 0
        End With
        
        .FormatConditions(1).StopIfTrue = True
        
     End With
      
End Sub

HTH

Conditional format based upon the date string

1 Answers1