Conditional Formatting with VBA

JayB0730

Board Regular
Joined
Oct 22, 2014
Messages
64
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
Try turning on the Macro Recorder, and apply this Conditional Formatting manually.
That will show you the exact VBA code you need to have.
 

JayB0730

Board Regular
Joined
Oct 22, 2014
Messages
64
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?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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).
 

JayB0730

Board Regular
Joined
Oct 22, 2014
Messages
64
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,976
Members
416,953
Latest member
broexc

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
Top