VBA code to select a variable range and then change the font colour

MrPaul412

New Member
Joined
Aug 4, 2010
Messages
18
Hi All,

What I'm trying to do is change the font colour of a range in a table which is dictated by a value in a cell.

Ie if cell a1 = 1 then select range b1:b5 change font to red
If cell a1 = 2 then select range b1:b10 change font to red

and so on....

any help with this would be much appreciated

Thanks
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
Have you explored the conditional formatting options? They should be able to help... Do a new formatting rule and select Use a formula to determine which cells to format. Press F1 to read through the help and try it out.
 
Upvote 0

MrPaul412

New Member
Joined
Aug 4, 2010
Messages
18
Thanks for the reply ztodd but in this case I wouldn't like to use conditional formatting as the sheet I'm using has many tabs and I would really like to use VBA code so I can select when to apply the formatting.
 
Upvote 0

ttdk1

Board Regular
Joined
May 21, 2014
Messages
189
Code:
Sub Example()
  Select Case ActiveSheet.Range("A1").Value
    Case 1
      ActiveSheet.Range("B1:B5").Font.Color = vbRed
    Case 2
      ActiveSheet.Range("B1:B10").Font.Color = vbRed
  End Select
End Sub
 
Upvote 0

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
You mean the workbook you're using has many sheets. :) Sorry I'm a stickler sometimes on terminology- Anyways, you can use the range.font.color property in VBA to change text color.

hah, guess i was too slow.
 
Upvote 0

MrPaul412

New Member
Joined
Aug 4, 2010
Messages
18
Your right sheets :)

thanks for your help!
 
Last edited:
Upvote 0

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,200
right click on sheet name and select view code. paste the below code

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Range("B:B").Font.Color = 0
Range("B1:B" & (Range("A1").Value * 5)).Font.Color = vbRed
End Sub
 
Upvote 0

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
People around here are too nice to give you the actual code... I guess I'm a mean guy- but I think you always ought to try coming up with a solution yourself before using someone else's code- it would be a benefit to you to do so.
 
Upvote 0

Forum statistics

Threads
1,191,576
Messages
5,987,397
Members
440,095
Latest member
yanaungmyint

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