Conditional formatting in a macro

[glennpc]

If I set up some conditional formatting and test it out in my spreadsheet and it works fine, can I expect it to work if I use the macro recorder and record a macro that creates the same conditional formatting, reset everything (delete all conditional formatting on the sheet) and then run the macro? I've done this and not gotten the same results. (When done manually, I format (in bold red) any cells that have a value that matches one in another range. So only some cells get formatted as expected-- it works. When I run it via the macro, it formats every cell in the range.)

Could it be that I already have other conditional formatting (on other ranges) already in place?

 

[Chihiro]

If you upload sample with data and your code (along with expected outcome). It will be easier for one of us to help you.

There are few things that could go wrong.
1. Record macro will grab fixed range only and may not format additional range added after the fact.
2. If recorded with "Use Relative Reference" it is important to have selection/cursor at appropriate location.
3. You may want to add line to remove all CF from sheet before you apply your code.

 

[glennpc]

Ok, I'm unable to attach my workbook-- it is too large.

On my worksheet, there are a row of buttons at the top of the worksheet to run the macros. I've already run macros to do the adding of blank columns and splitting the labels. The next set of buttons run macros that do some conditional formatting. They all work except for the Check States-- the green one on the right (and I run the Clear Conditional Formatting macro-- the dark red button--- in between running the other Conditional Formatting macros.)

I have the data I want the macro to test against in B4:B64. They are just US state codes (or territory codes) like AL, NJ, CA, etc. Two characters, all caps.

The StateCheck macro doesn't do what I want-- Here is the code for it (it operates on the Environment column which is AX):

Sub StateCheck()
'
' StateCheck Macro
' Checks the state values against column B and flags non-matches in red bold via conditional formatting
'
  Columns("AX:AX").Select
'
  Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
  "=ISERROR(MATCH(1,--EXACT($B$8:$B$64,AX8),0))"
  Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  With Selection.FormatConditions(1).Font
  .Bold = True
  .Italic = False
  .Color = -16776961
  .TintAndShade = 0
  End With
  Selection.FormatConditions(1).StopIfTrue = False
  ActiveWindow.SmallScroll Down:=-12
  
Range("AX8").Select
End Sub

Instead of only entries like Nj, nj, New Jersey, etc. in red (the only valid one is NJ), it is turning everything in the column red, including the legitimate ones.

 

[glennpc]

Actually, the range I'm testing against is B8:B64, so that part in the code is correct.

 

[glennpc]

I pared down my code so I could attach. On this simplified sheet, if I create a conditional formatting rule to check what's in column G against the allowable values in column C, it works fine. But when I create a macro that builds that same rule,using the exact same formula which is:

=ISERROR(MATCH(1,--EXACT($C$2:$C$12,G2),0))

and then run that macro, it highlights EVERY value in column G.

Any ideas why it works when you create it manually, but won't work when you use a macro to build it. (I clear out the formatting between runs.)

My workbook is attached.

 

[Chihiro]

Try changing your code to something like below to remove all CF before applying code.

However, screen won't update unless you save and re-open workbook or go to Conditional Format and Hit Edit without changing anything, hit "OK" and Apply.

I tried using ScreenUpdating but that didn't work.

Let me look into it a bit more.

Sub StateCheck()
'
' StateCheck Macro
' Checks the state values against column B and flags non-matches in red bold via conditional formatting
'
Dim lRow As Long
lRow = ActiveSheet.Range("AX" & Rows.Count).End(xlUp).Row

Cells.FormatConditions.Delete
ActiveSheet.Range("AX8:AX" & lRow).Select
'
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
  "=ISERROR(MATCH(1,--EXACT($B$8:$B$64,AX8),0))"
  Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  With Selection.FormatConditions(1).Font
  .Bold = True
  .Italic = False
  .Color = -16776961
  .TintAndShade = 0
  End With
  Selection.FormatConditions(1).StopIfTrue = False
  ActiveWindow.SmallScroll Down:=-12

Range("AX8").Select
End Sub

 

[glennpc]

Ok-- I subsequently sent you a workbook with something a lot smaller but it does the same thing. I'm wondering if this is a flaw in Excel.

 

[glennpc]

I ran your new code and it still conditionally formats every cell. Also, I have a separate macro that clears out all the formatting on the whole sheet and I've been running that too-- it doesn't seem to help.

 

[Chihiro]

It should CF all cell in range ("AX8:AX" & lRow) as per code.

However, it will somehow formats everything even if it does not evaluate to =ISERROR(). You have to first save and re-open file and you will see CF only applied to =ISERROR()=TRUE cells.

 

[glennpc]

Ok, thanks. I guess this is a flaw in Excel. The people I'm giving this sheet to probably won't want to save and close and then re-open to see the real results, but I don't see any other way to do this programmatically. Their choice will be to do the save-close-reopen, or create the conditional formatting manually.

Thanks for your help!