VB in Excel to change cell colour

vb6 Iran
  • 9 years ago

    Hi
    I'm trying to change the colour of a cell to red, yellow or green depending on the number inside that cell. If it has a 1 it needs to be red, 2 should be yellow and 3 green. I'm running the code when the sheet is activated. I have been able to write code which lets me do it but there are 32 items in each of 5 rows so the procedure ends up being too large.
    I think I am going to need a loop but haven't been able to work out how to perform the code on a range of cells.

    So far I've got:

    Range("g1").Select
    ActiveCell.FormulaR1C1 = Research1 'adds the variable number to the cell
    If Range("g1") = 1 Then
    Selection.Font.ColorIndex = 3
    Selection.Interior.ColorIndex = 3
    ElseIf Range("g1") = 2 Then
    Selection.Font.ColorIndex = 6
    Selection.Interior.ColorIndex = 6
    ElseIf Range("g1") = 3 Then
    Selection.Font.ColorIndex = 4
    Selection.Interior.ColorIndex = 4
    Else
    Selection.Font.ColorIndex = 39
    Selection.Interior.ColorIndex = 39
    End If

    Please help it's driving me crazy

    Thanks,
    Phil

  • 9 years ago

    Hi Phil,

    If you use the Cells(row,col) instead of Ranges, then stick it in a loop, you will get the effect you require:

    Sub CellColours()

    Dim i As Integer
    Dim j As Integer

    For i = 1 To 32
        For j = 1 To 5
            Cells(j, i).Select
            If Selection.Value = 1 Then
                Selection.Font.ColorIndex = 3
                Selection.Interior.ColorIndex = 3
            ElseIf Selection.Value = 2 Then
                Selection.Font.ColorIndex = 6
                Selection.Interior.ColorIndex = 6
            ElseIf Selection.Value = 3 Then
                Selection.Font.ColorIndex = 4
                Selection.Interior.ColorIndex = 4
            Else
                Selection.Font.ColorIndex = 39
                Selection.Interior.ColorIndex = 39
            End If
        Next j
    Next i

    End Sub

    Hope this helps!

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“If debugging is the process of removing software bugs, then programming must be the process of putting them in.” - Edsger Dijkstra