Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

problem vba CF when run the macro and close and open file again

0

Hi

this  problem  cause  for  me  big  headache .

the  code  works  perfectly   except  one  thing  is  when  highlight  by  condition formatting . the  code  compare  data  between  two  sheets   and  show  the  differences   values so  far  is  ok  but  from  requirements  the  code  should  show & highlight data  are  existed  in sheet  STOCK   but  not  existed  sheet  RPORT  , suprisingly  highlight  the  whole  items  in  column H in  sheet STOCK  but  when  save &  close  the  file   and  open  again  will  just  show  the  new & correct   items as  what i want  it  .in contrast some  friends  test  the  code  works  without  show  this  problem which  show  me .

in  beginning  I  thought  the  problem  from  the  file  and  create  new  file  but  the  problem  still  shows

I  need  solution  for  this  dilemma.

excel
MS Office Version: 2010
posted by: leopard (rep: 88) on Sat Jul 16, 2022 at 6:50 am
Answer
Discuss

Discussion

Hi Leopard

Don't have much time this weekend but the code seems to work okay for me -the conditional formatting shows red only for cells H11:H15, not the whole column.

Are you working with only this workbook open?
John_Ru (rep: 6152) Jul 16, '22 at 8:49 am
but the code seems to work okay for me -the conditional formatting shows red only for cells H11:H15, not the whole column.
 this is disappointed .
Are you working with only this workbook open?
of course 
leopard (rep: 88) Jul 16, '22 at 9:15 am
Sorry, is H11:H15 red the result your friends get but you do not? 
John_Ru (rep: 6152) Jul 16, '22 at 9:22 am
Sorry, is H11:H15 red the result your friends get but you do not? 

yes . this  is too  strange  case . seem  I  have  to  adapt  with this  problem .
thanks  for  your  time  to  try  help  me .  
leopard (rep: 88) Jul 16, '22 at 9:41 am
Did you try taking the CF formula from cell H2 i(as if editting the CF rule) , pasting it in E2 say and copying down? If it shows TRUE (but should not) , there may be a problem with your formula (or sheet names). I can't test that at present.
John_Ru (rep: 6152) Jul 16, '22 at 11:13 am
actually  I  did  it   but   doesn't  show  anything in  cells  for  column E. 
leopard (rep: 88) Jul 16, '22 at 4:43 pm
Please attach that (as second file to your question) and I will try to look at it tomorrow 
John_Ru (rep: 6152) Jul 16, '22 at 5:05 pm
I  put  the  formula  in  column E  to  check  the  red color  for  cells in column H  and  should  show   true but  doesn't  show  anything .
leopard (rep: 88) Jul 17, '22 at 5:23 am
Add to Discussion

Answers

0

Leopard

I can't replicate your error but in the first attached file, I have put your CF formula in E2:E20, this being the formula in H2...

=ISERROR(MATCH(H2,REPORT!$B$1:$B$11&" "&REPORT!$C$1:$C$11&" "&REPORT!$D$1:$D$11,0))
(you applied CF in that column).

You'll see that it produces a TRUE result when cells in H are not matched in the worksheet REPORT but also below the results (so rows 17:20) where a blank entry isn't found in the array..

When you get the column RED, this might be the cause so I suggest you change your rule to to give a FALSE result if H is blank. You can do this by wrapping it with an AND function around your rule (changes in bold for H2):

=AND(H2<>"",ISERROR(MATCH(H2,REPORT!$B$1:$B$11&" "&REPORT!$C$1:$C$11&" "&REPORT!$D$1:$D$11,0)))
.

In the second attached file, the new rule is in E2:E20 and the code applying the CF is changed to:

With .Columns(3)
    .Columns(0).Font.Name = "Wingdings 2"
    .FormatConditions.Delete
    .FormatConditions.Add 2, Formula1:="=AND(h2<>" & Chr(34) & Chr(34) & ",ISERROR(MATCH(h2," & _
            r(2).Address(, , , 1) & "&"" ""&" & r(3).Address(, , , 1) & _
            "&"" ""&" & r(4).Address(, , , 1) & ",0))"
    .FormatConditions(1).Interior.Color = vbRed
End With

Hope this helps.

posted by: John_Ru (rep: 6152) on Mon Jul 18, 2022 at 7:48 am
Discuss

Discussion

thanks  john  for  your  effort   and  try  to  help  me.   sorry   the  problem  doesn't  solve  it  .  but  the  value  true  &  false  keep  as  it  . just  highlight  the  whole  cells  when  run  the  macro  and  when  save  and  close  the  file and  open  again  show  highlight  the  right  cells.
leopard (rep: 88) Jul 18, '22 at 9:11 am
Leopard. Still not sure I understand but when the macro runs, E1:E20 of the second file (NEW 1 with revised CF rule v0_b.xlsm) should have these values in E1:E20...
Revised CF Rule
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
TRUE
TRUE
TRUE
TRUE
TRUE
FALSE
FALSE
FALSE
FALSE
FALSE
where only rows 11:15 (in bold) are TRUE (and would make CF show red).

It should be the same after the file is saved and re-opened. If not, I am lost!
John_Ru (rep: 6152) Jul 18, '22 at 10:07 am
John sorry  I  upset  you  with  this  strange  case  . all  of  what  you  said  extremely  right but about  this 
It should be the same after the file is saved and re-opened. If not, I am lost! when  open second  file   I  see  just  highlighting   rows 11:15 (in bold) are TRUE (and would make CF show red). this  is  correct  . the  dilemma  when  I  press  button  to  run  macro  surprisingly  highlight  the  the  whole  cells in  column  H  without  exclude  any  thing . I  try  to   delete  th CF  before  run  the  macro and  run  the  macro    but  nothing  succeed so  far . everybody  I  know  him  tested  the  code   works  without  showing  problem  as  in  my  case .  I barely  lose    my  mind !!!!
leopard (rep: 88) Jul 18, '22 at 10:25 am
Leopard- what do you mean by "second file"?   You should not need to clear CF before running your macro- this is done by your (bold line) line: 
With .Columns(3)
    .Columns(0).Font.Name = "Wingdings 2"
    .FormatConditions.Delete


Try changing your line: 
With .Rows(2).Resize(dic.Count)
to:
With .Rows(2).Resize(11)
. CF (and borders) should stop at row 12. 
John_Ru (rep: 6152) Jul 18, '22 at 10:41 am
You should not need to clear CF before running your macro- this is done by your (bold line) line:
I  know   but  this  problem  make  me  do   things  are  not  logical .
. CF (and borders) should stop at row 12. 
in this  case  highlight  from  cell H2: H12 and  ignore from H13:H15  
leopard (rep: 88) Jul 18, '22 at 5:00 pm
You say "in this  case  highlight  from  cell H2: H12 and  ignore from H13:H15" which proves that the CF is applied (normally) by the section starting 
With .Rows(2).Resize(dic.Count)
but if H2:H12 appear red, there is something wrong with how the rule is written in your code.

The problem is I can't see what and my (second) file works correctly for me.

Sorry Leopard but I don't know how to help you.
John_Ru (rep: 6152) Jul 18, '22 at 5:13 pm
The problem is I can't see what and my (second) file works correctly for me.
I  understand  that .
I  thought  this  can  be  from  office  version . I  tested  for  two  PC  and  different  versions 2016,2019   but   the  same  result   no  changes  the  problem  at  all . in  my  mind  guess  the  problem  is  from PC  setting  but  what  is  exactly  I no  know .
Sorry Leopard but I don't know how to help you.
I  ppreciate  for  your  time  and  try  to  help  me  for  this  dilemma . I  will  try  to  find  any  idea   in  the  internet   to  fix  this  dilemma.
leopard (rep: 88) Jul 19, '22 at 6:17 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login