Excel: VBA to apply Conditional Formatting to cells other than initial cell

[LesA]

Column B is a date column, Column C has 4 options, Column D is a numeric column.

If any one can help, I would like all 3 cells highlighted when inputs "Cancelled" from a drop-down menu in Column C.
I am currently using the VBA below to highlight Column C. Any help would be appreciated as I'm a VBA novice.

Private Sub Worksheet_Change(ByVal Target As Range)
Set I = Intersect(Target, Range("C5:C110"))
If Not I Is Nothing Then
Select Case Target
Case "Cancelled": NewColor = 44 ' Orange
End Select
Target.Interior.ColorIndex = NewColor
End If
End Sub

 

[vcoolio]

Hello LesA,

Try the code amended as follows:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Columns(3)) Is Nothing Then Exit Sub 'If required, Columns(3) can be hardcoded to Range("C5:C110").
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

If Target.Value = "Cancelled" Then
Range(Cells(Target.Row, "B"), Cells(Target.Row, "D")).Interior.ColorIndex = 44
End If

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[LesA]

Hello LesA,

Try the code amended as follows:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Columns(3)) Is Nothing Then Exit Sub 'If required, Columns(3) can be hardcoded to Range("C5:C110").
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

If Target.Value = "Cancelled" Then
Range(Cells(Target.Row, "B"), Cells(Target.Row, "D")).Interior.ColorIndex = 44
End If

End Sub

I hope that this helps.

Cheerio,
vcoolio.

Hi vcoolio,
That is brilliant and works perfectly and such a small bit of code.
However, if I hit the Delete key to remove "Cancelled" text, the colour remained. Is there a way to make the colour disappear when the text is deleted? Thank you so much for your help.
Les

 

[vcoolio]

Hello Les,

Are you actually deleting a row or just clearing the contents of the relevant cell in Column C?
If you are actually deleting a row, everything in the row, including formatting, will be deleted.

However, try the code amended as follows:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Columns(3)) Is Nothing Then Exit Sub 'If required, Columns(3) can be hardcoded to Range("C5:C110").
If Target.Count > 1 Then Exit Sub

If Target.Value = "Cancelled" Then
      Range(Cells(Target.Row, "B"), Cells(Target.Row, "D")).Interior.ColorIndex = 44
ElseIf Target.Value = "" Or Target.Value <> "Cancelled" Then
      Range(Cells(Target.Row, "B"), Cells(Target.Row, "D")).Interior.ColorIndex = xlNone
End If

End Sub

With the above amendment, the colouring should be removed if the relevant cell value in Column C is blank or does not ="Cancelled"

I hope that this helps.

Cheerio,
vcoolio.

 

[LesA]

Hello Les,

Are you actually deleting a row or just clearing the contents of the relevant cell in Column C?
If you are actually deleting a row, everything in the row, including formatting, will be deleted.

However, try the code amended as follows:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Columns(3)) Is Nothing Then Exit Sub 'If required, Columns(3) can be hardcoded to Range("C5:C110").
If Target.Count > 1 Then Exit Sub

If Target.Value = "Cancelled" Then
      Range(Cells(Target.Row, "B"), Cells(Target.Row, "D")).Interior.ColorIndex = 44
ElseIf Target.Value = "" Or Target.Value <> "Cancelled" Then
      Range(Cells(Target.Row, "B"), Cells(Target.Row, "D")).Interior.ColorIndex = xlNone
End If

End Sub

With the above amendment, the colouring should be removed if the relevant cell value in Column C is blank or does not ="Cancelled"

I hope that this helps.

Cheerio,
vcoolio.

Hi again vcoolio,
You are a genius!

Works perfectly just as you intended,
Thank you so much - Just after midnight here so will go off to bed a much happier person!
I would just be deleting the contents of the C cell (Cancelled) as the B column is for pre-determined dates and the D column is for input of count numbers.

 

[vcoolio]

Thanks for the kind words Les.

Same time zone so I'm finally off to bed also.

I'm happy to have been able to help.

Cheerio,
vcoolio.