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

[MrPaul412]

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

 

[ztodd]

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.

 

[MrPaul412]

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.

 

[ttdk1]

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

 

[ztodd]

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.

 

[MrPaul412]

Great, thanks. I'll try that when I'm back in the office.

 

[MrPaul412]

Your right sheets

thanks for your help!

 

[vds1]

right click on sheet name and select view code. paste the below 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

 

[ztodd]

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.