Obnoxious Conditional Formatting Bug - Please Help

[rdetreville]

Hello,

I have a worksheet where conditional formatting is set to highlight cells that appear in a column on another sheet. It works fine, but I have to click into each cell's formula bar and press enter for the conditional formatting to actually activate. The calculation mode is automatic. The data is general and is not text. This is the absolute last thing I need to fix on a project I've been working on day and night for two weeks - can someone please help? Code I used to set the conditional formatting is below, as well as a screen shot showing the cells which are updating conditional formatting and which are not. The ones that are still to the left have not been clicked on yet.

Thank you for your help!!

Conditional formatting code:

Sub revhighlight()
 Application.Calculation = xlAutomatic
  
 Dim r As Range

Set r = ActiveWorkbook.Worksheets("RevRec").Range("A3:A500")
r.FormatConditions.Add Type:=xlExpression, Formula1:="=vlookup(A3,'MENU'!$B$4002:$C$4300,1,false)"
r.FormatConditions(r.FormatConditions.Count).SetFirstPriority
With r.FormatConditions(1)
    .Interior.PatternColorIndex = xlAutomatic
    .Interior.ColorIndex = 6
    .Font.ColorIndex = 1
End With
r.FormatConditions(1).StopIfTrue = False

Set r = Nothing
    Application.ScreenUpdating = False
    Application.ScreenUpdating = True
End Sub

 

[RickXL]

Hi,

My questions would be:

1. Is your VLOOKUP really looking at column 1 of the table? =vlookup(A3,'MENU'!$B$4002:$C$4300,1,false)
2. What is in the table 'MENU'!$B$4002:$C$4300 ? I put numbers in column 1 and TRUE/FALSE in column 2 and it worked as I expected - after I had changed the 1 into a 2 in the vlookup.

 

[Jonmo1]

Sounds like the values are 'numbers stored as text'
Regardless of how the cells are formatted.
A Cell formatted as General can still actually contain a TEXT string, and not a number.

Try copying a blank cell
Highlighting your entire range of numbers (the cells you have to click in and press enter to make them work)
Right click - Paste Special - Values - Add - OK.

 

[rdetreville]

Hi RickXL!

1. Yes, it is really looking at Column 1. I was able to make a rudimentary workaround by replacing each cell in table with itself. Code below. If you double-click on each cell or click on each cell's formula bar and press return the formula works correctly and the correct conditional formatting shows. This data has made a long journey to this worksheet and I think perhaps something in the process causes Excel to view it funny. I also ran a code that runs down the column and verifies that each entry is in fact data/numerical and not just text.
2. The table data is a project number, which is a random 8-digit number, such as 20238800.


Here's the macro I used as a workaround for now - it seems to fix it for what I need it to do. Still don't know why it's not showing the data immediately though.

Sub fix2()
    Dim c As Range
 
    
    For Each c In ActiveWorkbook.Worksheets(3).Range("A3:A500")
        c.Formula = c.Formula
    Next c

end sub

 

[rdetreville]

Sounds like the values are 'numbers stored as text'
Regardless of how the cells are formatted.
A Cell formatted as General can still actually contain a TEXT string, and not a number.

Try copying a blank cell
Highlighting your entire range of numbers (the cells you have to click in and press enter to make them work)
Right click - Paste Special - Values - Add - OK.

Hi Jonmo1,

I actually used a macro earlier to verify the values weren't numbers stored as text - still didn't fix the problem. They're real numbers.

Here's the code I used:

<code>Sub macro()
Range("A:A").Select 'specify the range which suits your purpose
Sheets(2).Select
With Selection
    Selection.NumberFormat = "0"
    .Value = .Value
End With
End Sub</code>