Excel VBA if with mutiple columns values

[Apple08]

Hi All

I have a macro below to change the conditional format of the value in column B, however I need to extend it to include column E, G, H and J as well. Please could anyone help how should I update the macro below:

Private Sub IconSet()

    
    Dim x As Long, lastRow As Long

    lastRow = Cells(Rows.Count, "A").End(xlUp).row
    
    For x = lastRow To 2 Step -1
        If Cells(x, 2).Value = "Green" Then
            Cells(x, 2).Value = "1"
        End If
        
        If Cells(x, 2).Value = "Amber" Then
            Cells(x, 2).Value = "2"
        End If
        
        If Cells(x, 2).Value = "Red" Then
            Cells(x, 2).Value = "3"
        End If
        
            
      
    Next x


End Sub

 

[Joe4]

That isn't Conditional Formatting - that is direct formatting.
The negative about that is it isn't dynamic - if your data changes, the formatting won't change unless you manually run the code again.
Do you want the VBA code to actually be dynamic?

Also, are you looking at running this against existing data, or on new data as it is being entered?

 

[Apple08]

Thanks Joe4. This isn't a conditional format macro as I have another conditional format macro after I change the exist data from colour to number. Therefore I only require to have one macro to change the multiple columns RAG into numbers. this is running against existing data. Many thanks.

 

[Joe4]

OK, it is faster if we don't loop through each row one at a time (we don't need to do that, because we can replace on a whole range at once).

Try this:

Private Sub IconSet()

    Dim cols()
    Dim c As Long
    Dim lr As Long
    Dim rng As Range
    
    Application.ScreenUpdating = False

'   Set columns to apply to
    cols = Array("B", "E", "G", "H", "J")
    
'   Loop through each column
    For c = LBound(cols) To UBound(cols)
'       Find last row with data in column
        lr = Cells(Rows.Count, cols(c)).End(xlUp).Row
'       Build range
        Set rng = Range(Cells(1, cols(c)), Cells(lr, cols(c)))
'       First replacement
        rng.Replace "Green", "1"
'       Second replacement
        rng.Replace "Amber", "2"
'       Third replacement
        rng.Replace "Red", "3"
    Next c

    Application.ScreenUpdating = True

End Sub

 

[Apple08]

It's speedy and wonderful! Thanks very much for your help Joe4!

 

[Joe4]

You are welcome.
Glad I was able to help!