VBA - create the conditional formatting gradient colors

[GBagley]

I'm trying to get the colorindex values that would result in the conditional formatting gradient colors (shades red/yellow/green). I only need about 10 levels of granularity, so if you set up a sheet with 10 cells, values 1 through 10, and then conditional format them as a percentile gradient, then those are the colors I want to replicate. Any ideas?

I've already referenced the Chip Pearson, and other methods for getting at the CF colorindex, but I'm getting errors in those functions

http://www.xldynamic.com/source/xld.CFConditions.html#specific

Getting an error (type mismatch) @ this line:

For Each oFC In rng.FormatConditions</pre>

Full function:

'---------------------------------------------------------------------
Public Function CFColorindex(rng As Range)
'---------------------------------------------------------------------
Dim oFC As FormatCondition
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long

   Set rng = rng(1, 1)
   If rng.FormatConditions.Count > 0 Then
      For Each oFC In rng.FormatConditions
         If oFC.Type = xlCellValue Then
            Select Case oFC.Operator
               Case xlEqual
                  CFColorindex = rng.Value = oFC.Formula1
               Case xlNotEqual
                  CFColorindex = rng.Value <> oFC.Formula1
               Case xlGreater
                  CFColorindex = rng.Value > oFC.Formula1
               Case xlGreaterEqual
                  CFColorindex = rng.Value >= oFC.Formula1
               Case xlLess
                  CFColorindex = rng.Value < oFC.Formula1
               Case xlLessEqual
                  CFColorindex = rng.Value <= oFC.Formula1
               Case xlBetween
                  CFColorindex = (rng.Value >= oFC.Formula1 And _
                             rng.Value <= oFC.Formula2)
               Case xlNotBetween
                  CFColorindex = (rng.Value < oFC.Formula1 Or _
                             rng.Value > oFC.Formula2)
            End Select
         Else
             're-adjust the formula back to the formula that applies
             'to the cell as relative formulae adjust to the activecell
            With Application
               iRow = rng.row
               iColumn = rng.Column
               sF1 = .Substitute(oFC.Formula1, "ROW()", iRow)
               sF1 = .Substitute(sF1, "COLUMN()", iColumn)
               sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
               sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
            End With
            CFColorindex = rng.Parent.Evaluate(sF1)
         End If

         If CFColorindex Then
            If Not IsNull(oFC.Interior.ColorIndex) Then
               CFColorindex = oFC.Interior.ColorIndex
               Exit Function
            End If
         End If
     Next oFC
   End If 'rng.FormatConditions.Count > 0

End Function

 

[AlphaFrog]

Perhaps this would help.

Adding Color Scale to a Range of Values in Excel 2010

 

[GBagley]

Perhaps this would help.

Adding Color Scale to a Range of Values in Excel 2010

That's nice, but no, doesn't help. It's just programatically adding a gradient conditional formatting. I need to do this outside of excel's in-built conditional formatting.

I managed to copy the gradient into msPaint, grabbed the RGB colors for each of 12 gradient steps, and the converted those to ColorIndex numbers. So now I have the colors for each of the gradient steps. Just have the figure out the percentile thing...any ideas there? The percentile function only works with numbers between 0 and 1. How do I get a percentile for a range of number that are greater than 1? Trying to remember my statistics here...

 

[AlphaFrog]

PERCENTILE(array,k)*100

 

[GBagley]

Ah, I was using 'k' in the wrong way. I got it now