Replacing value based on cell color

[Nanditha]

Hi All,
I am looking for your help in the below scenario.

i am trying to replace values in column A if the font colour is green and it has a text “Expiring”. These should be replaced with cell value “Issued” and interior colour should be Amber.

Below is the code I have used. Can you please let me know if there is an error? I am not getting the result once the code is executed.

code attached in the image.

 

[Michael M]

It might help if you actually post the code rather than a picture of same....most posters wont want to rewrite the code to test it.

 

[Nanditha]

Sure. Here is the code

Dim Cl As Range

With Sheets(“CC5”)
For Each Cl In .Range(“A3”, .Range(“A” & rows.count).End(xlUp)
If Cl.cells.value = “Expiring” And Cl.font.color = -11489280 Then
Cl.cells.value = “Issued” And Cl.interior.color = 49407
End If

 

[Michael M]

Maybe this
Make sure that the font color references are correct

Sub MM1()
Dim Cl As Range
With Sheets("CC5")
    For Each Cl In .Range("A3", .Range("A" & Rows.Count).End(xlUp))
        If Cl.Value = "Expiring" And Cl.Font.Color = -11489280 Then
            Cl.Value = "Issued" And Cl.Interior.Color = 49407
        End If
    Next Cl
End With
End Sub

 

[Nanditha]

Maybe this
Make sure that the font color references are correct

Sub MM1()
Dim Cl As Range
With Sheets("CC5")
    For Each Cl In .Range("A3", .Range("A" & Rows.Count).End(xlUp))
        If Cl.Value = "Expiring" And Cl.Font.Color = -11489280 Then
            Cl.Value = "Issued" And Cl.Interior.Color = 49407
        End If
    Next Cl
End With
End Sub

Thank you soo much. This worked

 

[Michael M]

Glad to help ?