Macro to change colour font of an adjacent column

[princess21]

Hi,
I have a data tablet that has finance data for different areas of the company. There is probably a really easy solution for this but I am trying to write a macro that will change the colour font of the column that contains the actual "Expenditure" finance figures based on another column that tells me the "Performance". See below example

Performance Expenditure
Over £1500
Under -£1600
Under £700
Average £150
Over -£20000

The Expenditure column values can be positive or negative but I would like the font colour changed to the following depending on what is populated in the Performance column: Under = red, Average = amber, Over = green.
This will make the above table look like this:

Performance Expenditure
Over £1500 (green)
Under -£1600 (red)
Under £700 (red)
Average £150 (amber)
Over -£20000 (green)

As you can see, because the values can be positive or negative I can't just simply use conditional formatting and I think a macro is the only way to go.

Any help will be greatly appreciated.

Hugs

Princess

 

[Weaver]

You could use conditional formatting based on the content of the 'performance' column, maybe? Three possiblities would just fit the 2003 CF framework.

 

[princess21]

Thank you for your response. Unfortunately I NEED the colour fonting on the Expenditure column. The whole point of this spreadsheet is to make this actual figures stand out and to use the figures to highlight the over or under spend to the target audience. ​

 

[Weaver]

No problem.

You can use values of other cells for CF, not just the cell to be formatted.

Suppose B2 has data and C2 has text

select the column B from row 2 downwards

in CF, select 'formula is'

=C2="green"

and set the font colour

The other 2 should be similar

HTH

 

[princess21]

Say thanks but I need a much more robust solution because to conditionally format like this I would have to format each cell individually.
e.g. for B2 I will have to:
Sub Macro1()
Range("B2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$A$2=""Under"""
Selection.FormatConditions(1).Font.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$A$2=""Average"""
Selection.FormatConditions(2).Font.ColorIndex = 45
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$A$2=""Over"""
Selection.FormatConditions(3).Font.ColorIndex = 4
End Sub​

and for B3:
Sub Macro3()
Range("B2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$A$3=""Under"""
Selection.FormatConditions(1).Font.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$A$3=""Average"""
Selection.FormatConditions(2).Font.ColorIndex = 45
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$A$3=""Over"""
Selection.FormatConditions(3).Font.ColorIndex = 4
End Sub


Then I will have to repeat for B4, B5 and so on and so forth. Unfortunately I have hundreds of rows of data and currently over a dozen spreadsheets! with more generated each month! is there a way of converting this into a looped macro. Sorry but my VBA knowledge is quite basic.

 

[Weaver]

No you don't

Just select the whole range type in the formula

Just remember that if you start with cell B2 then use C2 in your formula, if it's B10 then use C10.

Seriously.

Just try it.

 

[Weaver]

PS id you really need to apply the formatting with a macro, try:

Sub Macro1()
    With Range(Cells(Rows.Count, "B").End(xlUp), "B2")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$A2=""Under"""
        .FormatConditions(1).Font.ColorIndex = 3
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$A2=""Average"""
        .FormatConditions(2).Font.ColorIndex = 45
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$A2=""Over"""
        .FormatConditions(3).Font.ColorIndex = 4
    End With
End Sub

Writing this was a bigger headache than just selecting the cells and applying the formatting, but if you're not going to use the code, make sure when you input your formula, don't 'lock' the row, i.e use $A2 rather than $A$2

 

[princess21]

Whoops. School boy error. Made the change and it's all worked! Thanks for the help.

 

[Weaver]

Whoops. School boy error. Made the change and it's all worked! Thanks for the help.

No problem - made me think about it - CF is very different from 2003 to 2007 and beyond.