Hi, all, new to this group!
I have a spreadsheet with dozens of worksheets. Each worksheet contains a series of data with two different totals columns (the data is arranged 2 ways per sheet). The two totals columns should equal if all works correctly in the data, which is linked via SumProduct. I want to do a little sanity check that will color the interior of one of the total columns bright red if the totals do not match, and I'd prefer to establish the conditional formats programmatically so as to avoid establishing them individually for each sheet.
The code to determine the location of the cell to apply the conditional formatting works perfectly, as does the code to determine which cell should be compared. However, at this point in time, when I click on the cell and click on Conditional Formatting, the formatting has a hard-coded value. Since the data will change monthly and I'd prefer to avoid running this macro each month, I'm wondering if it's possible for the conditional formatting to be established so that it references a cell location rather than its value?
works, but this sets the formatting to be the hard-coded value:
Cell Value is Not Equal To 2.1918
sets up the formatting to be:
Cell Value is Not Equal To ="$K$25"
In order for it to work as I hope it will, it needs to be:
Cell Value is Not Equal To =$K$25
(no quotation marks).
I tried using the Indirect() function, but apparently that doesn't work in VBA. Any suggestions?
I know it is possible to run the code each month as a subroutine when the data is loaded and reset the values in example 1 above, but the code to run the data is lengthy and I'd like to avoid additional steps.
I have a spreadsheet with dozens of worksheets. Each worksheet contains a series of data with two different totals columns (the data is arranged 2 ways per sheet). The two totals columns should equal if all works correctly in the data, which is linked via SumProduct. I want to do a little sanity check that will color the interior of one of the total columns bright red if the totals do not match, and I'd prefer to establish the conditional formats programmatically so as to avoid establishing them individually for each sheet.
The code to determine the location of the cell to apply the conditional formatting works perfectly, as does the code to determine which cell should be compared. However, at this point in time, when I click on the cell and click on Conditional Formatting, the formatting has a hard-coded value. Since the data will change monthly and I'd prefer to avoid running this macro each month, I'm wondering if it's possible for the conditional formatting to be established so that it references a cell location rather than its value?
Rich (BB code):
Set rg = Range(Cells(x, totalcol2).Address)
rg.FormatConditions.Delete
rg.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlNotEqual, Formula1:=totrg.Value rg.FormatConditions(1).Interior.ColorIndex = 3works, but this sets the formatting to be the hard-coded value:
Cell Value is Not Equal To 2.1918
Rich (BB code):
Set rg = Range(Cells(x, totalcol2).Address)
rg.FormatConditions.Delete
rg.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlNotEqual, Formula1:=totrg.Address rg.FormatConditions(1).Interior.ColorIndex = 3sets up the formatting to be:
Cell Value is Not Equal To ="$K$25"
In order for it to work as I hope it will, it needs to be:
Cell Value is Not Equal To =$K$25
(no quotation marks).
I tried using the Indirect() function, but apparently that doesn't work in VBA. Any suggestions?
I know it is possible to run the code each month as a subroutine when the data is loaded and reset the values in example 1 above, but the code to run the data is lengthy and I'd like to avoid additional steps.
