Coloring column cells based on values

Deepk

Board Regular
Joined
Mar 21, 2018
Messages
100
Try this one for the second inquiry :

Code:
Sub FillNumbers()
    firstVal = 0
    cellColor = 0
    
    For Each c In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
        
        If c.Interior.Color <> cellColor Then
            firstVal = firstVal + 1
            cellColor = c.Interior.Color
        End If
        Cells(c.Row, 1).Value = firstVal
        
    Next c
End Sub
A heartiest thank you Peter. Both codes are running fine. :)
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Deepk

Board Regular
Joined
Mar 21, 2018
Messages
100
Code:
Sub FillGreyColor()
    
    firstVal = 0
    modulo = 0
    
    Range("A:A").Interior.Color = xlNone
    
    For Each c In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        
        If c.Value <> firstVal Then
            modulo = modulo + 1
            firstVal = c.Value
        End If
        
        If modulo Mod 2 = 1 Then
            c.Interior.Color = 12566463
        End If
        
    Next c
End Sub
Hi louisH,

will it be possible to modify this code so that it can work for

work for selected range of cells in a column
color cells including the corresponding cells of next column
remove existing color if any in both columns before color them

Thank you.
 

louisH

Board Regular
Joined
Mar 14, 2018
Messages
152
Hi louisH,

will it be possible to modify this code so that it can work for

work for selected range of cells in a column
color cells including the corresponding cells of next column
remove existing color if any in both columns before color them

Thank you.
Yes I can tinker Something like that :
Changes made :

-It will only work for the selected range of cells
-It will remove color whithin selection
-It will color selection + column to the right of selection

Code:
Sub FillGreyColor()
    
    firstVal = 0
    modulo = 0
    
    Dim col As Double
    col = Selection.Column
            
    If Selection.Columns.Count > 1 Then
        MsgBox "Select only 1 column"
        Exit Sub
    End If
    
    Selection.Interior.Color = xlNone
    Selection.Offset(0, 1).Interior.Color = xlNone
    
    For Each c In Selection
        
        If c.Value <> firstVal Then
            modulo = modulo + 1
            firstVal = c.Value
        End If
        
        If modulo Mod 2 = 1 Then
            Range(Cells(c.Row, col), Cells(c.Row, col + 1)).Interior.Color = 12566463
        End If
        
    Next c
    
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,074
Messages
5,466,464
Members
406,483
Latest member
Shlammed

This Week's Hot Topics

Top