VB Colours

[wildturkey]

Hi

I'm using the following to apply conditional formatting (picked up from an old thread) and it works great but the colours are pretty vivid and less transition from one colour to the next - not like the nice colour scale that conditional formatting offers. Is there any way to improve on this please - I've tried colorindex but no joy there...

Sub ApplyCF()

    Dim UpperColour As Long, LowerColour As Long
    Dim StartRow As Long, EndRow As Long, CurrentRow As Long
    Dim StartColumn As Long, EndColumn As Long
    Dim Target As Range
   
    ' ******  Adjust as required  ******
   
    UpperColour = vbGreen
    LowerColour = vbWhite
   
    StartRow = 1
    EndRow = 3000
   
    StartColumn = 1
    EndColumn = 20
   
    ' **********************************
   
    For CurrentRow = StartRow To EndRow
   
        ' The Target range spans from the start column to the end column on iterative rows.
        Set Target = Range(Cells(CurrentRow, StartColumn), Cells(CurrentRow, EndColumn))
       
        ' Sets the conditional formatting rule for the range, Target
        With Target
            .FormatConditions.AddColorScale ColorScaleType:=2
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
           
            ' Sets the colour for the lower value
            .FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue
            .FormatConditions(1).ColorScaleCriteria(1).FormatColor.Color = LowerColour
           
            ' Sets the colour for the upper value
            .FormatConditions(1).ColorScaleCriteria(2).Type = xlConditionValueHighestValue
            .FormatConditions(1).ColorScaleCriteria(2).FormatColor.Color = UpperColour
        End With
    Next
   
    MsgBox "Complete."
   
    Set Target = Nothing
   
End Sub

 

[mikerickson]

You can use the RGB fuction to set the colors. This would set them to what they are currently, but you could massage them as desired.

UpperColor = RGB(0, 255, 0) ' same as vbGreen
LowerColor = RGB(255, 255, 255) ' same as vbWhite

 

[wildturkey]

Works nicely thank you