VBA conditional formatting

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
644
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Code:
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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.
 
Upvote 0
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:

Code:
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"
 
Last edited:
Upvote 0
Upvote 0
Hi tyija1995

Thanks for the prompt response.

I tried this, but it didn't work?

Did you actually run it?
 
Upvote 0
Hi Mr2017,

Yep I tried and ran it, works fine on my machine, what error are you getting when trying to run it?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
Actually, I found it!

rgbOrange!

It's been a while since I used colors!

Thanks again for your help with formatting!
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top