PDA

View Full Version : Solved: Clear Red Interior if Text is Red



akapol1955
02-04-2011, 03:24 PM
I found a macro here which converts any Tracked Changes to a Red font. A spreadsheet has been sent to our clients for updates, and some have used a red cell interior instead of red text to highlight their changes.
As is evident, running the macro causes those cells to have red text and a red interior, which causes a problem with legibility.

How would I search for all cells in a Workbook which have both red text and a red interior, and set the interior to XLColorIndexNone?

Thanks very much from a VBA novice - you all rock!

Kenneth Hobs
02-04-2011, 05:32 PM
Sub InteriorColorRedToNone()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.Interior.Color = 255 And cell.Font.Color = 255 Then
cell.Interior.Color = xlNone
End If
Next cell
End Sub

akapol1955
02-07-2011, 10:37 AM
Kenneth:
Thanks very much for your help -- the code works like a charm! One more favor: is it possible to get it to process every worksheet in a workbook when it is run instead of a single sheet at a time?
Thanks again!
Bob

Kenneth Hobs
02-07-2011, 10:43 AM
Please mark thread solved if so.


Sub InteriorColorRedToNone()
Dim cell As Range, ws As Worksheet
For Each ws In Worksheets
For Each cell In ws.UsedRange
If cell.Interior.Color = 255 And cell.Font.Color = 255 Then
cell.Interior.Color = xlNone
End If
Next cell
Next ws
End Sub

akapol1955
02-07-2011, 11:02 AM
Thank you, Kenneth (a fine name as that was my dad's name.. :) )
It works perfectly. I thought I had done things propertly to run it on all sheets but was apparently incorrect.
Hope you have a great day!
Bob