Selecting a dynamic range and coloring it.......

[nynamyna]

Hello everybody,

I have an excel sheet in which there are seven columns. I will sort them using different combinations of criteria1 column and criteria2 column and give different colors to them. For example

criteria1 criteria 2 color
1 1 yellow
1 0 green
0 1 pink
0 0 blank

I recorded this as as a macro but, selecting the ranges for coloring becomes fixed. So if the file has more values then it is coloring only half of it. So this coloring should be done dynamically. Can anyone help ?

Thanks,
Bharath <!-- / message --><!-- attachments -->

 

[pbornemeier]

This is one way to do it:

Sub ColorA1CurrentRegion()

    Dim rngCell As Range
    Dim iCriterion1 As Integer
    Dim iCriterion2 As Integer
    
    For Each rngCell In Range("A1").CurrentRegion
        
        'Determine cell criteria values
        If rngCell.Value > 3 Then iCriterion1 = 1 Else iCriterion1 = 0
        If rngCell.Value = 2 Or rngCell.Value = 9 Then iCriterion2 = 2 Else iCriterion2 = 0
        
        'Modify for headers
        If Not IsNumeric(rngCell.Value) Then
            iCriterion1 = 0
            iCriterion2 = 0
        End If
        
        Select Case iCriterion1 + iCriterion2
        Case 0: rngCell.Interior.Color = vbWhite
        Case 1: rngCell.Interior.Color = vbRed
        Case 2: rngCell.Interior.Color = vbGreen
        Case 3: rngCell.Interior.Color = vbYellow
        End Select
    Next
    
End Sub

If your columns do not contain A1 then the address in the For statement must be changed to an appropriate value.