Code to change interior colour only if current interior colour is

[Guest]

Hi all
I need a code that finds cells on the current worksheet that are currently
set with an interior colour index of 36, and reset them to be xlnone.
By doing so I'm getting rid of standard shading but keeping user applied
colours.

I tried to do the following but it doesn't apply to all cells???

Sub NoYellowshading()
Dim lastrow, crow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "a").End(xlUp).Row
Range("A1:BY" & lastrow).Select
If Selection.Interior.ColorIndex = 36 Then
Selection.Interior.ColorIndex = xlNone
End If
End Sub

Any help greatly appreciated.

 

[Bob Phillips]

Sub NoYellowshading()
Dim lastrow, crow As Long
Dim r As Range
Dim cell As Range
lastrow = ActiveSheet.Cells(Rows.Count, "a").End(xlUp).Row
For Each cell In Range("A1:BY" & lastrow).
If cell.Interior.ColorIndex = 36 Then
cell.Interior.ColorIndex = xlNone
End If
Next cell
End Sub

 

[Peter T]

I don't follow the distinction of "getting rid of standard
shading but keeping user applied colours" but have a go
with this:

Sub NoYellowshading2()
Dim r As Range
For Each r In ActiveSheet.UsedRange
If r.Interior.ColorIndex = 36 Then
r.Interior.ColorIndex = xlNone
End If
Next
End Sub

UsedRange assumes you mean ALL cells.

Regards,
Peter