VBA format of cells with colour based on IF formula

T

Tony

I have an IF formula that provides a “Y†value if a cell date matches a
vlookup table of statuory holidays which works fine.

wsPh.Range("F7:AD7").Formula =
"=IF(ISNA(VLOOKUP(F5,StatHolidays,2,FALSE)),"""",""Y"")"

Based on that date I want to use code that will colour cells in a column.
Right now the shading does not work even though the “Y†exists on the screen.
I also copied the value from H7 and paste special it back into H7 and it
still didn’t work.

If h7 = "Y" Then
wsPh.Range("h9:h60").Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Else
End If

P.S. is there any way to combine the “With†criteria onto one (1) line
instead of three (3)
 
T

Tony

It is okay I figured out my problem.

If wsPh.Range("h7") = "Y" Then

instead of


If h7 = "Y" Then
 
S

Sheeloo

What is h7 in your code (line If h7 = "Y" Then)?
You should use
If wsph.Range("h7") = "Y" Then
assuming wsph refers to the worksheet you want to color

No, you can not combine the rows since you are setting two differenct
properties.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top