vba or conditional formatting!!

miyat

Board Regular
Joined
Nov 27, 2009
Messages
166
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...
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
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

Board Regular
Joined
Nov 27, 2009
Messages
166
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

Board Regular
Joined
Nov 27, 2009
Messages
166
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

Board Regular
Joined
Aug 14, 2007
Messages
95
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

Well-known Member
Joined
Jul 4, 2004
Messages
7,480
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,806
Messages
5,598,188
Members
414,218
Latest member
speedbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top