Community discussion forum

VB in Excel to change cell colour

• • Reply

  3 months ago

  by microb

  microb

  , Iran

  Joined 7 months 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
• Advertisement

  Simply the fastest line-level profiler for .NET ever

  “The low overhead means it has minimal impact on the execution of my program”
  Mark Everest, Development Team Leader, Renault F1 Team Ltd.

  Try out the new ANTS Profiler 4 for yourself. Download your 14-day trial now

• • Reply

  3 months ago

  by Uncle

  Uncle

  England, United Kingdom

  Joined 6 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