Solved: Clear Specific InteriorColor Index

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

I'm trying to put some code togther which searches column 'AE' on my spreadsheet starting at row 5 to row 2000. If the code finds the word 'Yes', I would like to clear any cell shading from columns B:Q on the same row if the Interior.ColorIndex = 35

I've started to put some code together, shown below, which clears all the cell formatting, but I'm not sure how to specify the colour to look for and then how to clear this interior colour:

If Target.Column = 31 Then
If Target.Value = "Yes" Then
Range("B" & Target.Row & ":Q" & Target.Row).ClearFormats
End If
End If
I just wondered if someone could have a look at this please and offer a little guidance on how I may go about achieving this.

Sincere thanks and regards

[Kenneth Hobs]

That is easily done but did you want it for a Change event?

Why not run a macro to fix it first and then use conditional formatting rather than a Change event?

[hobbiton73]

Hi @Kenneth Hobbs, thank you for taking the time to reply to my post.

If at all possible I'd like to do it via a Change event please. This piece of code fits into a bigger script which I've included below.

Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range, res As Variant
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("B5:Q2000")) Is Nothing Then
If Target.Value <> preValue And Target.Value <> "" Then
Application.EnableEvents = False
Range("A" & Target.Row).Value = Date
Range("AE" & Target.Row).Value = "No"
Application.EnableEvents = True
Target.ClearComments
' Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "dd-mm-yyyy") & Chr(10) & "By " & Environ("UserName")
Target.Interior.ColorIndex = 35
End If
End If
On Error GoTo 0

If Not Intersect(Target, Range("I5:I2000")) Is Nothing Then
Set Cell = Worksheets("Lists").Range("B2:C23")
res = Application.VLookup(Target, Cell, 2, False)
If IsError(res) Then
Range("J" & Target.Row).Value = ""
Else
Range("J" & Target.Row).Value = res
End If
End If

If Target.Column = 8 Then
If Target.Value = "E" Or Target.Value = "P" Then
Target.Offset(, 1).Value = "Enter_Project_or_Enhancement_Code"
Target.Offset(, 2).Value = "Enter_Description"
Else
Target.Offset(, 1).Value = ""
Target.Offset(, 2).Value = ""
End If
End If

If Target.Column = 8 Then
If Target.Value = "OH" Then
Target.Offset(, 3).Value = "N/A"
Else
Target.Offset(, 3).Value = ""
End If
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then preValue = Target.Value
End Sub
I did try to make the change with conditional format, but I think, although I'm happy to be proven wrong, that this didn't work because the Interior.ColourIndex 35 is set via code.

Many thanks and kind regards

[Bob Phillips]

Untested

Option Explicit

Private preValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range, res As Variant

If Target.Cells.Count > 1 Then Exit Sub

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range("B5:Q2000")) Is Nothing Then

If Target.Value <> preValue And Target.Value <> "" Then

Range("A" & Target.Row).Value = Date
Range("AE" & Target.Row).Value = "No"
Application.EnableEvents = True
Target.ClearComments
' Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "dd-mm-yyyy") & Chr(10) & "By " & Environ("UserName")
Target.Interior.ColorIndex = 35
End If
ElseIf Not Intersect(Target, Me.Range("I5:I2000")) Is Nothing Then

Set Cell = Worksheets("Lists").Range("B2:C23")
Me.Range("J" & Target.Row).Value = ""
If Not IsError(Application.VLookup(Target.Value, Cell, 2, False)) Then

Me.Range("J" & Target.Row).Value = Application.VLookup(Target.Value, Cell, 2, False)
End If
ElseIf Target.Column = 8 Then

If Target.Value = "E" Or Target.Value = "P" Then

Target.Offset(, 1).Value = "Enter_Project_or_Enhancement_Code"
Target.Offset(, 2).Value = "Enter_Description"
Else

Target.Offset(, 1).Value = ""
Target.Offset(, 2).Value = ""
If Target.Value = "OH" Then

Target.Offset(, 3).Value = "N/A"
Else

Target.Offset(, 3).Value = ""
End If
End If
ElseIf Target.Column = 31 Then

If Target.Value = "Yes" And Target.Interior.ColorIndex = 35 Then

Target.Interior.ColorIndex = xlColorIndexNone
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then preValue = Target.Value
End Sub

[hobbiton73]

Hi @xld, thank you very much for taking the time to reply to my post and for putting the solution together.

Unfortunately I couldn't get this to work, and it also took away some of the functionality which was working before implementation.

I have however re-visited the sheet, and I've found a way to this via Conditional Formatting.

Once again, thank you for your time and trouble.

Kind regards