conditional color format

[Fidelachius]

I have 3 cells that I need to format to change only the font not the backround based on:
1. Is it > 33.33%?
2. Is the money tied to that % the highest of the 3?


35% - 45% - 20%
40k - 50k - 30k

so 45% would be, for example, green as it is >33% and also the highest money of the 3. Then 35% would be yellow being middle and then 20% would be red. The % values always = 100% total is that helps at all.

I know there is color formate red-yellow-green that you can do but that changes the backround and I dont know how to change the rule.

Appreciate any help!

 

[noboffinme]

Hi Fidelachius,

I created a file that has 2 columns, Column 'A' has various Percentages & Column 'B' has Currency amounts.

If you create a similar file & paste the below code into a Macro enabled workbook, you should get what you're after.

The code searches Column 'B' for the Max & Min amounts & then moves up Column 'B' from the bottom Row (500) to Row 2 & colours the Font in Column 'A' according to your rules.

Let me know if this is what you're after.

Sub cond_format_font()
Dim rng As Range
Dim min As Double
Dim max As Double
Dim i, j As Integer
Set rng = Sheet1.Range("B2:B500")
min = Application.WorksheetFunction.min(rng)
max = Application.WorksheetFunction.max(rng)
j = Application.CountA(Sheets("Sheet1").Range("B:B"))
Cells.Range("B" & j).Select
For i = 2 To j
    Cells.Range("B" & j).Select
    If ActiveCell.Value = min Then
    Range("B" & j).Cells.Offset(0, -1).Select
    ActiveCell.Font.Color = -16776961 'RED
        
    ElseIf ActiveCell.Value = max Then
    Range("B" & j).Cells.Offset(0, -1).Select
    ActiveCell.Font.Color = -11489280 'GREEN
        
    ElseIf ActiveCell.Value <> min Or max Then
    Range("B" & j).Cells.Offset(0, -1).Select
    ActiveCell.Font.Color = -16711681 'YELLOW
    End If
    
    j = j - 1
       
Next i
End Sub

 

[Fidelachius]

Wow thank you! I will definately play with this. I did work out today to just add another column that would rank the % and then based on what rank would color the % column.

I think this will be a bit easier since I wont have a 3rd info source

 

[noboffinme]

No problem