VBA conditional formatting

[Mr2017]

Hi Guys

I need to use conditional formatting with VBA to change the colour of certain cells.

I found the code below on this site, but need to format it so it changes the color of the cell rather than the number format.

So if the word "Low" appeared in a cell, I'd want it to be red, for example - please see the draft (incomplete) code that I've written (also below). Can you use the "add color scale" method in this instance? If so, what should be typed afterwards? I've tried "vb" then the color, but that didn't work.

A complete Sub would be greatly appreciated! Thanks in advance.

Sub Macro1()

LastRw = Range("F" & Rows.Count).End(xlUp).Row

For i = 1 To LastRw
If Range("F" & i).Value = 1 Then
Range("F" & i).NumberFormat = """UP"""
Else
Range("F" & i).NumberFormat = "General"
End If
Next
End Sub

Sub CndFrm()


LastRw = Range("P" & Rows.Count).End(xlUp).Row


For i = 1 To LastRw
    If Range("P" & i).Value = "Low" Then
        Range("P" & i).FormatConditions.AddColorScale
    Else
        Range("F" & i).NumberFormat = "General"
    End If
Next
End Sub

 

[tyija1995]

Hey, you can test the colour of cells using VBAs .Interior.Color property in a MsgBox.

Using this technique I have that an orange cell has a colour number of 49407 - so try that.

Sub TestColour()
    MsgBox Selection.Interior.Color
End Sub

I used this code to get the colour of the selected cell in question (I manually applied an orange fill then tested the macro with the cell selected)

NOTE: The Color will be between 0 and 256^3 (16777216)

 

[Mr2017]

Ok, cool - thanks!

I’ll try that out tomorrow!