vba or conditional formatting!!

[miyat]

hi guys, do u have a vba or a steps for conditional formatting for this? which is dependent on sheet number? example F4 value that i will input is different every sheet number...

if F4=6, the font color of D72:D73 & B104:I104 will become WHITE...
if F4=5, the font color of D70:D73 & B103:I104 will become WHITE...
if F4=4, the font color of D68:D73 & B102:I104 will become WHITE...
if F4=3, the font color of D66:D73 & B101:I104 will become WHITE...
if F4=2, the font color of D64:D73 & B100:I104 will become WHITE...
if F4=1, the font color of D62:D73 & B99:I104 will become WHITE...

NOTE: different value of F4 for every sheet number that is ranging from 6-1..
and the cell to be change in font color is the same as given above...

 

[dave3009]

Well if you wanted to use CF then you woul highlight your chosen ranges and use Format>>Conditional Formatting>>Formula Is =$F$4=6 and apply your desired formatting

 

[miyat]

hi, CF wont work... is there any other way? i hope there's a cod for this... i where do i place it.... tnx....

 

[miyat]

hi guys, do u have a vba or a steps for conditional formatting for this? which is dependent on sheet number? example F4 value that i will input is different every sheet number...

if F4=6, the font color of D72:D73 & B104:I104 will become WHITE...
if F4=5, the font color of D70:D73 & B103:I104 will become WHITE...
if F4=4, the font color of D68:D73 & B102:I104 will become WHITE...
if F4=3, the font color of D66:D73 & B101:I104 will become WHITE...
if F4=2, the font color of D64:D73 & B100:I104 will become WHITE...
if F4=1, the font color of D62:D73 & B99:I104 will become WHITE...

 

[sosullivan]

Hi miyat,

Try the following code

Sub FontColor()
Select Case Range("F4").Value
Case 6: Range("D72:D73", "B104:I104").Font.ColorIndex = 2
Case 5: Range("D70:D73", "B103:I104").Font.ColorIndex = 2
Case 4: Range("D68:D73", "B102:I104").Font.ColorIndex = 2
Case 3: Range("D66:D73", "B101:I104").Font.ColorIndex = 2
Case 2: Range("D64:D73", "B100:I104").Font.ColorIndex = 2
Case 1: Range("D62:D73", "B99:I104").Font.ColorIndex = 2
Case Else
End Select
End Sub

Hope this helps

Seamus

 

[jim may]

You might want to insert this 1 line of code immediately AFTER "Sub FontColor()" to clear
any previous run of the code

Range("D62:I104").Font.ColorIndex = xlAutomatic