VB in Excel to change cell colour

vb6 Iran
  • 12 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

  • 12 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.

“Weeks of coding can save you hours of planning.”