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

 

[Joe4]

Try turning on the Macro Recorder, and record yourself perforning the Conditional Formatting manually, and that should give you the VBA code that you need to do it.

By the way, it shouldn't be necessary to do it row-by-row. You can apply Conditional Formatting to a whole range at once.

 

[tyija1995]

Just as Joe says, use the macro recorder to generate the template, it will add a lot of code that isn't needed, I have recorded and tidied up the code a bit so feel free to use the following:

Sub CFVBA()
'
' CFVBA Macro
'


'
    Range("P1:P5").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""Low"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = vbRed
    End With
End Sub

Just change the P1:P5 range to whatever range you require and it should be good to go!

This code will change any cell in the range of P1:P5 to vbRed if it equals "low"

 

[Mr2017]

Hi Joe4

Thanks for the prompt response.

Whenever I try to record a macro in Excel 2016 with Windows 10, it always crashes then Excel re-starts.

Do you know what the settings I should change for it not to crash, without having to delete temporary files and re-starting?

I've come across some suggested solutions online (like the one below) but they weren't clear...?
https://superuser.com/questions/1339012/excel-2016-restart-itself-when-i-try-recording-a-macro

 

[Mr2017]

Hi tyija1995

Thanks for the prompt response.

I tried this, but it didn't work?

Did you actually run it?

 

[tyija1995]

Hi Mr2017,

Yep I tried and ran it, works fine on my machine, what error are you getting when trying to run it?

 

[Joe4]

It sounds like you either have some sort of corruption going on, or have something interfering with (could be Anti-Virus, or any host of other programs).
The first thing I would probably do is to try it on a brand new Excle file. If thart works, then the issue is probably with your Excel file (corruption).

Secondly, make sure that you are up-to-date on all Office patches. If issue continues, you may want to try re-installing Excel/Office.

 

[Mr2017]

Actually, apologies tyija1995 !

That code you wrote does actually work!

I had to make a change to some of the text in my file.

But, otherwise the code works!

Thanks for posting it!

 

[Mr2017]

Hi tyija1995

Do you know what the color name is for Orange in VBA?

I've tried "vborange" but the intellisense doesn't autocorrect the "o" at the beginning to a capital "O" which makes me believe "vbOrange" doesn't exist in VBA?

Do you know how to refer to it, please?

 

[Mr2017]

Actually, I found it!

rgbOrange!

It's been a while since I used colors!

Thanks again for your help with formatting!