| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Format based on existing cell colour ? | Excel Discussion (Misc queries) | |||
| Is there a way to add cells based on their shading / fill colour? | Excel Discussion (Misc queries) | |||
| How can I colour format all cells based on their values | Excel Worksheet Functions | |||
| How do I set a colour to 4 cells based on the value of a cell | Excel Discussion (Misc queries) | |||
| Adding colour to a range of cells based on one of the cells v... | Excel Discussion (Misc queries) | |||
Linear Mode
