VBA format of cells with colour based on IF formula
 

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 didnt 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)

VBA format of cells with colour based on IF formula
 

It is okay I figured out my problem.

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

instead of


If h7 = "Y" Then


"Tony" wrote:


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 didnt 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)

VBA format of cells with colour based on IF formula
 

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.

"Tony" wrote:


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 didnt 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)