force EXCEL to Update the Conditional Formatting rules

[drom]

Hi and thanks in advance

I know How to put:

  Application.Calculation = xlCalculationAutomatic
  Application.screenUpdating = true
  Calculate
  Range("A1").Activate
  Range("B10").Select
  ActiveWindow.ScrollRow = 1
  ActiveWindow.ScrollRow = 100
  Sheet(2).Select
  Sheet(1).activate

I have conditional formating applyed to a few cells, say: B1:B50

When I run a Macro I put in a few cells B1:B50 the FileDateTime(of Range("a1:a50")) 'This WORKS PERFECT
range("A1:A50") shows the FullName of my files and every single Workbook present in A1:50 EXISTS
I apply the Formatting to the cells B1:B50 as follows:
=AND(($F$1+TIME(2;10;0))< B1;B1<>"") the cell fill colour is RED
$F$1= shows a datetime


But No matter what I put in my Macro, the Conditional Formatting never shows the Changes, Puts every single cell in B1:B50 in RED

The Conditional Formatting never Updates, never shows with no RED colour, I mean in Blank, the cells should be in Blank

To Update I have to Select manually the cells I know for sure they should not be red

Actually if put a in Range("A1") a non existing FullName the Conditional Formatting WORKS, because B2 is EMPTY, but for the rest, no way

Is this a BUG??

How can I force EXCEl to Update the Conditional Formatting rules??

Thanks again

 

[Joe4]

Re: force EXCEl to Update the Conditional Formatting rules

[COLOR=#333333]=AND(($F$1+TIME(2;10;0))[/COLOR]<b1;b1< style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">"") </b1;b1<>

It looks like maybe your formula is being truncated, because that does not look like a valid formula. Can you try again?
Note that greater than and less than signs are often mistaken for HTML code on internet forums. The way to avoid that is to put a single space on either side of the sign, i.e.
A > B
B < A

 

[drom]

Re: force EXCEl to Update the Conditional Formatting rules

The problem in my file is not the formula.
is true the formula I pasted was wrong.
I am actually using:
=AND(($F$1+TIME(2;10;0))<A1;A1<>"")
but my query is still the same

 

[Joe4]

Re: force EXCEl to Update the Conditional Formatting rules

I am actually using:
=AND(($F$1+TIME(2;10;0))<a1;a1< style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">"")</a1;a1<>

Can you see the formula that you posted?
It is not coming across correctly (it is not a valid formula, as it is currently shown).

If you use greater than or less than signs in your formulas, when you post the formula here, they often get mistaken for HTML code and totally chop off/mess up the formula.
If you put spaces on both sides of those signs, then the formula will post here correctly.

So, can you try posting your formula again, doing this?

 

[RoryA]

Re: force EXCEl to Update the Conditional Formatting rules

Joe,

I've edited the original post to show the full formula.

 

[Joe4]

Re: force EXCEl to Update the Conditional Formatting rules

Can you provide some samples that are not working correctly?
Please provide the value in F1, and some of the values in column B not working correctly.

Also, are you certain that all your values entered in F1 and column B are entered as date/time, and not as text?
An easy way to check is with this formula:
=ISNUMBER(B1)
as dates/times are really stored in Excel as numbers.

If any return FALSE, they are not valid date/time entries (they are text), so the date/time functions will not work on them.

 

[drom]

Re: force EXCEl to Update the Conditional Formatting rules

=AND((A1 > $F$1+TIME(2;10;0)); A1 <> "")
Looks like the BB Code changes my formula
so in case:

=AND((A1 biger than $F$1+TIME(2;10;0)); A1 different blank "")