changing font colours in adjcent cells

[spurs100]

Hi all,

Is there a way in VBA to change the font colour of adjcent cells. For example if cell A1 font is red i want cells a2 and a3 to have a red font aswell. I am relatively new to VBA, can anyone help?

Many thanks

 

[dwilson01]

This does what you are asking, it could probably be cleaned up for you specific needs. Anyway, here it is

ActiveSheet.UsedRange
For Each oCell In ActiveSheet.UsedRange.Columns(1).Cells
  Range(Cells(oCell.Row, oCell.Offset(0, 1).Column), _
    Cells(oCell.Row, oCell.Offset(0, 2).Column)).Font.ColorIndex _
    = oCell.Font.ColorIndex
Next oCell

If you need more help, post it here.

dwilson01

 

[shep6]

Here is what I have used in the past, this will change the font color of cells A1 and B1 to our companies' corporate colour, using the Red Green Blue function:


Cells(1,1).Select
Selection.Font.Color = RGB(70, 104, 228)
ActiveCell.Offset(0, 1).Select
Selection.Font.Color = RGB(70, 104, 228)

cheers