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.
--
Thank for your help
BeSmart

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

--

HTH

RP

"BeSmart" wrote in message
...
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.
--
Thank for your help
BeSmart

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

-----Original Message-----
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.
--
Thank for your help
BeSmart
.