CODEWITHSUNDEEP
vbaexcel
Lucas Morin
Lucas Morin

Reputation: 398

VBA remove green text: Font.ThemeColor

I have these worksheets where I used green text as abbreviations to request databases, the complete name are for users only. I thought it was a good idea to use a different color so these comments can be removed easily. I've managed to take a look at all used cells in my worksheets.

What I want to do now is a test on the color of the font.

On Error Resume Next
If cl.Font.ThemeColor = xlThemeColorAccent3 Then
    cl.Value = ""
End If
On Error GoTo 0

EDIT:

I've tried this:

For Each WS In WB.Worksheets
For Each cl In WS.Range("A1:H10").Cells

    On Error GoTo Nextiteration

    If cl.Font.ThemeColor = xlThemeColorAccent3 Then
        cl.Value = ""
    End If

Nextiteration:

Next
Next

But it remove all colored cell in my range.

Can you explain me why ? Can you show me how to select only green cells (olive green, accent 3)

Upvotes: 2

Views: 2209

Answers (1)

Anirudh Ramanathan
Anirudh Ramanathan

Reputation: 46728

It is due to the way you have placed your error handling code. When If cl.Font.ThemeColor = xlThemeColorAccent3 Then throws an exception, it is ignored and the execution continues on the next step.

Attempting to access a theme color for an object whose color is not currently themed will result in an invalid request run-time error.

Use Err.Number to find out if an error is thrown.

----Loop----

On Error Resume Next
If cl.Font.ThemeColor = xlThemeColorAccent3 Then
    If Err.Number = 0 Then ' will be 0 only if no error occurred in the previous step    
         cl.Value = ""
    End If
End If
Err.Clear ' Clear error state

----End Loop----

Upvotes: 4

Related Questions