Conditional gradient formatting per row

[50ld13r]

HI,

I want to do gradient formatting across the row for each row in my dataset. I have about 1000 rows so don't want to be going crazy with the format painter. It looks like I cant use relative references in the min/max formulas so was wondering how to do it. Ideally I want to avoid helper cells and VBA. Seems that it should be simple but cant figure it out.

Thanks

 

[50ld13r]

Yes this exactly what I am after. How have you implemented it?

 

[Dan_W]

Macro. It took a second to run over 100 rows. Basically, all it does is sets the conditional formatting rules for you. You do not need to send anyone a macro-enabled workbook. I've prepared the code below so that all you would need to do is adjust the start/end rows/columns and colouring. Is that ok?

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

 

[50ld13r]

Need to test it - think I get what you're saying now. Provided the conditional formatting is preserved they can update the data their end and it will still work as the same rule set still applies

 

[Dan_W]

Yes. You can still send it to them in an XLSX file format. The only difference is that VBA is doing the work that you would otherwise be doing yourself.

 

[Dan_W]

How did it go?

 

[Crane]

Macro. It took a second to run over 100 rows. Basically, all it does is sets the conditional formatting rules for you. You do not need to send anyone a macro-enabled workbook. I've prepared the code below so that all you would need to do is adjust the start/end rows/columns and colouring. Is that ok?

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

Hi, Dan.

The script is fantastic, saved me a lot of time! Thank you so much.

May I ask, if I wanted to have three color sets, what part do I edit? I would like to have Green, Yellow and Red, not as gradient.

Also, in terms of the formatting criteria, it does take each row as independent of each other, but the number range does not seem to be from minimum to max. For instance, if I have 12000 as the min and max for a given row, it still colors it with the upper color rather than the lower one since there is no number lower than 12000.

 

[Dan_W]

It's been a while since I looked at this, but I'm glad it has come in handy.

I would like to have Green, Yellow and Red, not as gradient.

But what is it that would define what is Green, Yellow and Red? It is possible to to have a three color gradient. Is that what you're after?

 

[Crane]

It's been a while since I looked at this, but I'm glad it has come in handy.

But what is it that would define what is Green, Yellow and Red? It is possible to to have a three color gradient. Is that what you're after?

A three-color gradient, yes. It should take the low, mid and high, out of all the data points in a given row. I think this is the same behavior/logic in the existing gradient/formatting option, only problem is we can't "tell" it to apply for each row or look at each row as independent of each other.

 

[Dan_W]

A three-color gradient, yes. It should take the low, mid and high, out of all the data points in a given row. I think this is the same behavior/logic in the existing gradient/formatting option, only problem is we can't "tell" it to apply for each row or look at each row as independent of each other.

Well that's what confuses me, because you can see from the screenshot I posted in reply#10 above that this is what my code does. It applies the conditional formatting only in respect of each row.

As for your requirements, are you saying that only three cells should be formatted?

 

[Crane]

Sorry for the confusion, Dan. I used your code and that worked for me.

My inquiry was to know how I may customize it more, such as adding one more color. In your script, there are only two - low and high. I would like to have a middle color.

About the application of formatting only in respect of each row - this works perfectly. The only odd thing I noticed is that when for instance I have 5 numbers in a row and all of them are 12000, all the numbers are colored with the UpperColour, rather than the LowerColour. If there are no other numbers in the row, 12000 in this case should be lower. This is not an issue with what I'm doing right now but would be great to know how to alter.

All in all, considering the absence of such feature in Excel - conditional formatting only in respect of each row - what you have shared helped a lot. Thank you.