Why is saving, closing and reopening necessary for the VBA to work correctly?

[Eloise T]

Attached is an Excel file with a VBA module named:
Conditional_Format_ Reset

When you open the attached file, Column C from Row 4 to Row 23 has no background color.
If you open the [Macro window] using ALT + F8, and Run Conditional_Format_ Reset, only the first "variable" of the each of the named ranges (see Name Manager) receives a background color. ...they all are intended to get color.

If you save and reopen the file, all of the colors appear as they should. Why don't all the colors appear at once when the first variable of each named range appears and all the colors appear once you save, close and reopen the file?

 

[Hui]

Interesting problem

I've read several solutions on the internet and none apply or work in your situation

I also tried a different formula: =MAX(IFERROR(SEARCH(SearchFor70,$C3),0))=1

Because you are apply the CF using VBA have you considered changing the formula so that they aren't array entered

ie: instead of =Or(Isnumber(SEARCH(SearchFor70,$C3)))

Apply each individually
=Or(Isnumber(SEARCH("D70",$C3)))
=Or(Isnumber(SEARCH("E70",$C3)))
=Or(Isnumber(SEARCH("KL70",$C3)))
etc

 

[Eloise T]

I was kind of hoping not to have to do that.

Can I stack them like that?... one right after the other in the VBA code?

Like this:

.FormatConditions.Add Type:=xlExpression, Formula1:="=OR(ISNUMBER(SEARCH(D70,$C3)))"
.FormatConditions.Add Type:=xlExpression, Formula1:="=OR(ISNUMBER(SEARCH(E70,$C3)))"
.FormatConditions.Add Type:=xlExpression, Formula1:="=OR(ISNUMBER(SEARCH(KDL70,$C3)))"
.FormatConditions.Add Type:=xlExpression, Formula1:="=OR(ISNUMBER(SEARCH(LC70,$C3)))"
.FormatConditions.Add Type:=xlExpression, Formula1:="=OR(ISNUMBER(SEARCH(LC-70,$C3)))"
.FormatConditions.Add Type:=xlExpression, Formula1:="=OR(ISNUMBER(SEARCH(M70,$C3)))"
.FormatConditions.Add Type:=xlExpression, Formula1:="=OR(ISNUMBER(SEARCH(PNC70,$C3)))"
.FormatConditions.Add Type:=xlExpression, Formula1:="=OR(ISNUMBER(SEARCH(PNL70,$C3)))"
.FormatConditions.Add Type:=xlExpression, Formula1:="=OR(ISNUMBER(SEARCH(PNR70,$C3)))"
.FormatConditions(.FormatConditions.Count).Font.ColorIndex = 1        'Black
.FormatConditions(.FormatConditions.Count).Font.Bold = False
.FormatConditions(.FormatConditions.Count).Font.Italic = False
.FormatConditions(.FormatConditions.Count).Font.TintAndShade = 0
.FormatConditions(.FormatConditions.Count).Interior.ColorIndex = 3    'Red
.FormatConditions(.FormatConditions.Count).Interior.Pattern = xlPatternLinearGradient

 

[Hui]

I would try it on a small area on one worksheet
Always try it on a copy of the file, just in case

 

[NARAYANK991]

Hi ,

Array formulas work , without the usage of CTRL SHIFT ENTER , in named ranges and Conditional Formatting.

Narayan

 

[Debaser]

Try this formula instead:

Formula1:="=SUMPRODUCT(ISNUMBER(SEARCH(SearchFor70,C3))+0)"

and adjust as necessary for the other named ranges.

 

[Eloise T]

Try this formula instead:

Formula1:="=SUMPRODUCT(ISNUMBER(SEARCH(SearchFor70,C3))+0)"

and adjust as necessary for the other named ranges.

Outstanding! That's what it needed.

 

[Eloise T]

Hi ,

Array formulas work , without the usage of CTRL SHIFT ENTER , in named ranges and Conditional Formatting.

Narayan

Thank you for your help...yet again!
I figured out that your code just needed a couple tweaks for it to work.
FYI: The most significant was to remove .LinearGradient. and replace it with simply .Gradient. ...and I was just guessing when I tried it.

I am curious if there's a way to "force" it to 5003 rows in Conditional Format "Applies to?"

...or better yet, how does it decide how many rows in the "Applies to" ???

I have uploaded file should you care to see it working.

Needless to say, I would have not been successful without your help...and Debaser for the formula.

 

[NARAYANK991]

Hi ,

If you are sure that you want the CF rules to be applied to the range C3:C5003 , then change the following lines of code :

lLastRow = ws.Cells(Rows.Count, 3).End(xlUp).Row - 2
With ws.Range("C3:C" & lLastRow)

to the following single line of code :

With ws.Range("C3:C5003")

Narayan

 

[Eloise T]

Thank you for the fix! Also, ....

 

[NARAYANK991]

Hi ,

That's right. Actually the subtraction of 2 to get the last row of data is not correct. What it actually returns is the number of rows , since your first row is row #3.

Thus , if your last row of data were to be row #570 , then the actual number of rows of data would be :

570 - 3 + 1 = 570 - 2

You can remove the -2 from the line of code.

Narayan

 

[Eloise T]

Hi ,

That's right. Actually the subtraction of 2 to get the last row of data is not correct. What it actually returns is the number of rows , since your first row is row #3.

Thus , if your last row of data were to be row #570 , then the actual number of rows of data would be :

570 - 3 + 1 = 570 - 2

You can remove the -2 from the line of code.

Narayan

Thank you!