Conditional Formatting with VBA

JayB0730

Board Regular
Joined
Oct 22, 2014
Messages
74
Office Version
  1. 365
Platform
  1. Windows
Hello,

I'm having trouble with part of my VBA code. I'm up to the part where I am highlighting a range of cells within the current row and adding formatting (i.e. borders). Additionally, I want to add conditional formatting of that same range based on a TRUE/FALSE statement of a hidden cell within that row. Here is the current code; however, it will not update for each row:

Code:
 With Selection.FormatConditions.Add( _        Type:=xlExpression, _
        Formula1:="=OR($J8=TRUE,$I8>0)")

I'm assuming I need to change the cell references but not sure how. Any help is greatly appreciated!

Thank you,
Jay
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Try turning on the Macro Recorder, and apply this Conditional Formatting manually.
That will show you the exact VBA code you need to have.
 
Upvote 0
Try turning on the Macro Recorder, and apply this Conditional Formatting manually.
That will show you the exact VBA code you need to have.

Hi Joe,

Thank you for your help! I must have done something wrong last time because it did not work; however, I produced different results this time. I had to also convert the reference values to R1C1 format & that did the trick. Here is the final code for anyone looking to do something similar:

Code:
Sub test()'
' test Macro
'


'
    With Selection.FormatConditions.Add(Type:=xlExpression, Formula1:= _
        "=IF(R[]C8=TRUE,TRUE,FALSE)")
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

One last question, am I correct to assume there is no way around Excel creating a new conditional rule each time I run this macro? Basically, when I create a new row of data, I am applying this conditional formatting to that selected row which creates a new rule in CF each time. Thoughts?
 
Upvote 0
One last question, am I correct to assume there is no way around Excel creating a new conditional rule each time I run this macro? Basically, when I create a new row of data, I am applying this conditional formatting to that selected row which creates a new rule in CF each time. Thoughts?
Why not just select the whole range you want to apply it to initially?
You could pick the entire columns, or maybe just gone down to a row number you will never hit (i.e. format the first 1000 rows if you know you will never go over 1000 rows).
 
Upvote 0
Why not just select the whole range you want to apply it to initially?
You could pick the entire columns, or maybe just gone down to a row number you will never hit (i.e. format the first 1000 rows if you know you will never go over 1000 rows).

Hi,

I'm creating a financial checkbook/ledger where I would like to include an "add" button that would add a new row each time a new entry is to be made.
 
Upvote 0
That's fine, but you can still set up the formatting for that range ahead of time (so it is in effect before you add any rows).
It doesn't need to be part of the VBA code.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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