Macro needed to update cell value based on cell shading or font colour

[jmclaren]

Hi,

I receive a large spreadsheet that uses cell shading and font colours to indicate if an item is required or expired. It would be a lot easier to review if I had a macro that looked at all the data cells in the worksheet and did the following:

If a cell has no data *and* is shaded, then insert a value of "Reqd"
If a cell has text with a red font, replace the text/value with "Expired"

Unfortunately, I have no idea how to go about creating a macro to do this and any help would be much appreciated.

Thanks
Jim

 

[John Davis]

This considers all cells on the Sheet, but it can be confined to a Column.

Sub jmclaren()
Dim r As Range
For Each r In ActiveSheet.UsedRange
    If r.Interior.ColorIndex = xlNone And r = "" Then r.Value = "Reqd"
    If r.Font.ColorIndex = 3 Then r = "Expired"
Next r
End Sub

 

[jmclaren]

This considers all cells on the Sheet, but it can be confined to a Column.

Sub jmclaren()
Dim r As Range
For Each r In ActiveSheet.UsedRange
    If r.Interior.ColorIndex = xlNone And r = "" Then r.Value = "Reqd"
    If r.Font.ColorIndex = 3 Then r = "Expired"
Next r
End Sub

Wow, quick response . That worked a treat...just needed to change to "If not(r.Interior.ColorIndex = xlNone) And r = "" Then r.Value = "Reqd""
Many thanks for your help John.

 

[John Davis]

You're welcome. Glad to help out and thanks for your feedback.