Thread: Format a cell according to adjacent cell value

Hi.. as you can see I am new to the forum. I am still learning excel and I don't know if I'll ever be great at it but, still plugging away. I am searching through the threads now but, I decided to also post my question since I haven't found my question asked yet.

Excel 2007 or 2003:
I am trying to write a macro to have the contents of column A be in a certain font color(blue) if the number in the adjacent cell in column B is = 100. This will be applied to the entire columns (172 cells or more). I will be doing this to 7 other columns in the sheet too. I've tried to record the macro but, it just isn't running correctly?
Thanks for your time

You can do this without a macro, using conditional formatting. The process is slightly different in 03 and 07.

Originally Posted by StephenR

You can do this without a macro, using conditional formatting. The process is slightly different in 03 and 07.

Hi StephenR,
Thanks for replying!!!! I am using 07 for now. I don't know if all the users who will view this sheet have 07 or 03. I tried using conditional formatting in 07 too, I just couldn't figure out how to design it so that if cell B7=100 then cell A7 is to be a font of blue. Then I would apply that rule to 150 plus cells in 14 different columns. example column A looks at B, column C looks at D, E looks at F - etc. etc.

An alternative approach would be code which is triggered whenever a relevant cell is changed - perhaps that would be easier as common to both versions? So people might change the entries in col B and you want A's to change accordingly?

Originally Posted by StephenR

An alternative approach would be code which is triggered whenever a relevant cell is changed - perhaps that would be easier as common to both versions? So people might change the entries in col B and you want A's to change accordingly?

Thanks, Yes, I agree StephenR. I am trying to figure out how to write that code. I guess it would be something like: If B2: = 100 then A2 Selection.Font.ThemeColor = xlThemeColorLight2

I guess something like that. But, I am not the greates VB person. The other trouble is that it would apply to all cells in B and adjacent cells in A

This might give you an idea - in the sheet module so right-click the sheet tab, View Code and paste. Every time you change something in B1:B100 it will change font colour in A.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B1:B100")) Is Nothing Then
    If Target.Value = 100 Then
        Target.Offset(, -1).Font.Color = vbBlue
    Else
        Target.Offset(, -1).Font.Color = vbBlack
    End If
End If

End Sub