Coloring Excel cells under conditions

Question

I'm trying to find a code in excel macro that can address the following problem.

If the first column contains any text then highlight the first column cells with a certain color until next text doesn't appear in the same column. When any text appears in the same column, start coloring the cells with different color.

I have to repeat this for all the worksheets in my workbook. Thanks.

enter image description here

Right now I'm using this macro to colour the cells which are empty but the problem is the color doesnot change whenever a text is encountered

Sub try()
Dim i As Integer
Dim j As Integer
Dim k As Integer
i = 200
j = 100
k = 5

Application.ScreenUpdating = False

With ActiveSheet.UsedRange
    .AutoFilter Field:=1, Criteria1:=""
    If WorksheetFunction.CountBlank(.Columns(1)) > 0 Then
        .Columns(1).SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(i, j, k)
    Else
        i = i - 50
        j = j - 10
        k = 255
    End If

    .AutoFilter
End With

Application.ScreenUpdating = True

End Sub

to color the cell's background color, try cells.interior.color = RGB(255,0,0) 'Red — Larry
Please explain more clearly what you are trying to achieve. The example you give shows nothing but a yellow A, a green B, and a blue C. What does this mean? Try something, then get back to us with any specific problems. Voting to close. — Jean-François Corbett
as i'have told earlier i have to color the rows until a text is encountered and then change the colour — Cprog
"If the first column contains any text then highlight the first column cells with a certain color until next text doesn't appear in the same column." -> This is what makes it confusing and does not make sense. — html_programmer
if first column contains a text keep on highlighting the cells of that column. if again in the same column text appears follow the same procedure with different colour. it is concerned with one column only — Cprog

html_programmer html_programmer · Accepted Answer · 2012-10-25T14:12:11

Here you go:

Option Explicit

Sub Color_Ranges()

Dim oSheet                  As Worksheet
Dim oRange                  As Range
Dim oRange_Color            As Range
Dim oBaseCell               As Range
Dim lLast_Row               As Long
Dim lRange_Rows             As Long
Dim iCnt_Values             As Integer
Dim iCnt_Intervals          As Integer

Dim r                       As Integer
Dim g                       As Integer
Dim b                       As Integer

Dim iCnt                    As Integer


Set oSheet = ThisWorkbook.Sheets(1)
With oSheet
    lLast_Row = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
End With

'Total range you want to color
Set oRange = oSheet.Range(Cells(1, 1), Cells(lLast_Row, 1))
lRange_Rows = oRange.Rows.Count

'Count values
iCnt_Values = WorksheetFunction.CountA(oRange)
'Count intervals
iCnt_Intervals = iCnt_Values - 1

'Generate random colors
r = CInt(Int((255 * Rnd()) + 1))
g = CInt(Int((255 * Rnd()) + 1))
b = CInt(Int((255 * Rnd()) + 1))

Set oBaseCell = oRange.Cells(1, 1)
For iCnt = 1 To iCnt_Intervals
    Set oRange_Color = Range(oBaseCell, oBaseCell.End(xlDown))
    oRange_Color.Interior.Color = RGB(r, g, b)
    r = CInt(Int((255 * Rnd()) + 1))
    g = CInt(Int((255 * Rnd()) + 1))
    b = CInt(Int((255 * Rnd()) + 1))
    Set oBaseCell = oBaseCell.End(xlDown)
    Set oRange_Color = Nothing
Next iCnt

End Sub

Coloring Excel cells under conditions

1 Answers