VBA Highlight cells based on cell content

[eksplosion310]

I'm working on getting this macro working. I want it to, for every row, when "Late" is entered into column C, to highlight the cell 2 spaces to the left and Range of cells 3 spaces to the right through 43. So example is C4 contains "Late", highlight A4 and F4:AW4. Same goes for the word "Hold" just a different color.

Sub Macro1()
    Const TEST_COLUMN As String = "D"
    Dim LastRow As Long
    Dim cell As Range
    sSheetName = ActiveSheet.Name
    
    With Worksheets(sSheetName)
        LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
        
        For Each cell In Range("C:C" & LastRow)
            If cell.Value = "LATE" Then
                cell.Offset(, -2).Resize(, 21).Interior.ColorIndex = 39
                cell.Offset(, 3).Resize(, 21).Interior.ColorIndex = 39
            ElseIf cell.Value = "HOLD" Then
                cell.Offset(, -2).Resize(, 21).Interior.ColorIndex = 43
                cell.Offset(, 3).Resize(, 21).Interior.ColorIndex = 43
            Else
                cell.EntireRow.Interior.ColorIndex = xlNone
            End If
        Next
    End With
    
End Sub

▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

​

 

[Marc L]

Hi !

For cell A4 no need to Resize ‼ And "LATE" is not "Late" …

sSheetName variable is useless ‼ With ActiveSheet …
And if code is within a standard module, ActiveSheet is useless too !
​

 

[NARAYANK991]

Hi ,

See if this does what you want.

Sub Macro1()
    Const TEST_COLUMN = "D"
  
    Dim LastRow As Long
    Dim cell As Range
  
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
      
        For Each cell In .Range("C1:C" & LastRow)
            If UCase(cell.Value) = "LATE" Then
                cell.Offset(, -2).Interior.ColorIndex = 39
                cell.Offset(, 3).Resize(, 44).Interior.ColorIndex = 39
            ElseIf UCase(cell.Value) = "HOLD" Then
                cell.Offset(, -2).Interior.ColorIndex = 43
                cell.Offset(, 3).Resize(, 44).Interior.ColorIndex = 43
            Else
                cell.EntireRow.Interior.ColorIndex = xlNone
            End If
        Next
    End With
End Sub

Narayan

 

[shrivallabha]

For what it's worth, the same can be achieved through Conditional Formatting. Do you have any specific reason for not doing this through available native features.