Color Scales

[psycoperl]

Good Afternoon,
Normally I partake in the MS Access section of this board, however I have a quandary in Excel.

I would like to have the Max value used for determining the cell's highlight when using the "Graded Color Scale" to be based on the value of Column F for each row. How can i do this?

 

[Dryver14]

are you saying you have determined the max value in column F?

If so just select that range in column F and you should see a box appear on the bottom right of the selection.

click on it and select databars

 

[psycoperl]

The cells that I want to have colored are in Column G. Column F only has the "Max Value"

So the color Effect of Green for being close to the Max and Red being closer to 0 would apply to each value of Col G based on the max value listed in Col F of each row.

A sample table:

COL E	COL F	COL G
1	6000	5000
2	7000	6300
1	6000	3000
1	6000	1523
1	6000	1000
2	7000	5000
3	250	200
3	250	100
1	6000	1000
2	7000	250

 

[Worf]

Try this:

Sub Format_Column_G()
Dim r As Range, fc, i%
Set r = [h:h]                                                ' helper column
r.FormatConditions.Delete
r.FormatConditions.AddColorScale ColorScaleType:=3
r.FormatConditions(r.FormatConditions.Count).SetFirstPriority
Set fc = r.FormatConditions(1)
fc.ColorScaleCriteria(1).Type = 1                            ' lowest
fc.ColorScaleCriteria(1).FormatColor.Color = 7039480
fc.ColorScaleCriteria(2).Type = 5                            ' percentile
fc.ColorScaleCriteria(2).Value = 50
fc.ColorScaleCriteria(2).FormatColor.Color = 8711167
fc.ColorScaleCriteria(3).Type = 2                            ' highest
fc.ColorScaleCriteria(3).FormatColor.Color = 8109667
[h1] = 0
For i = 2 To Range("g" & Rows.Count).End(xlUp).Row
    [h2] = Cells(i, 6) / 100
    [h1:h2].AutoFill [h1:h101], xlFillDefault
    [h102] = Cells(i, 7)
    Cells(i, 7).Interior.Color = [h102].DisplayFormat.Interior.Color
Next
End Sub