VB in Excel to change cell colour

• • Reply

  12 years ago

  by microb

  boy

  , Iran

  Joined 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

  Report abuse
• • Reply

  12 years ago

  by Uncle

  Uncle

  England, United Kingdom

  Joined 19 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!

  Report abuse