Obnoxious Conditional Formatting Bug - Please Help

rdetreville

New Member
Joined
Jul 23, 2014
Messages
38
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:

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


nvp5x5.jpg
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,324
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Jul 23, 2014
Messages
38
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.

Code:
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

New Member
Joined
Jul 23, 2014
Messages
38
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:
<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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,992
Members
414,115
Latest member
SFUser

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