Quick excel macro question

tres · 18 Feb 2009 at 20:53

I have this macro

Code:

Sub auto_open()

   ' Run the macro DidCellsChange any time a entry is made in a
   ' cell in Sheet1.
   ThisWorkbook.Worksheets("Sheet1").OnEntry = "DidCellsChange"

End Sub


Sub DidCellsChange()
  Dim KeyCells As String
   ' Define which cells should trigger the KeyCellsChanged macro.
   KeyCells = "B3:AD3, B5:AD5, B7:AD7, B9:AD9, B11:AD11, B13:AD13, B15:AD15, B17:AD17, B19:AD19, B21:AD21, B23:AD23, B25:AD25, B27:AD27, B29:AD29, B31:AD31, B33:AD33, B35:AD35, B37:AD37, B39:AD39, B41:AD41, B43:AD43, B45:AD45, B47:AD47"

   ' If the Activecell is one of the key cells, call the
   ' KeyCellsChanged macro.
   If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
   Is Nothing Then KeyCellsChanged

End Sub

Sub KeyCellsChanged()
   Dim Cell As Object
   ' If the values in A11:C11 are greater than 50...
   For Each Cell In Range("B3:AD3")
   If Cell.Value = "H" Then

   ' Make the background color of the cell the 3rd color on the
   ' current palette.
   Cell.Interior.ColorIndex = 3
    Cell.Font.ColorIndex = 3
    
   Else
   
      If Cell.Value = "T" Then

   ' Make the background color of the cell the 3rd color on the
   ' current palette.
   Cell.Interior.ColorIndex = 41
    Cell.Font.ColorIndex = 41
    
   Else
   
      If Cell.Value = "G" Then

   ' Make the background color of the cell the 3rd color on the
   ' current palette.
   Cell.Interior.ColorIndex = 4
    Cell.Font.ColorIndex = 1
    
   Else
   
         If Cell.Value = "" Then

   ' Make the background color of the cell the 3rd color on the
   ' current palette.
   Cell.Interior.ColorIndex = xlNone
    Cell.Font.ColorIndex = 1
    
   Else
   
   
   ' Otherwise, set the background to none (default).
   Cell.Interior.ColorIndex = xlNone
    Cell.Font.ColorIndex = 1
    
   End If
   End If
   End If
   End If
   
   Next Cell

End Sub

Which changes the cell color based on value

But if i clear the contents by pressing "delete" or similar, it remains the color of the previously entered value

I've tried changing the last IF argument to "", ClearContents and Empty, but no joy.

Any ideas?

kkelly · 18 Feb 2009 at 21:00

Hi there,

You macro is only trigged when you open the workbook, have you had a look at coniditional formating within excel this will do what you are after and is always on

Hope this helps

Kevin.

tres · 18 Feb 2009 at 21:02

ah, i thought of that but need to change the font colour as well as the cell colour!

kkelly · 18 Feb 2009 at 21:05

You could try using the "Worksheet_change" method inside you VBA then

, I dont have excel at home so I am guessing

this should do the trick

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If ActiveCell.Value = "5" Then
Code
end if

End Sub

Dolph · 19 Feb 2009 at 10:17

You could use the worksheet change method, but if you tend to edit data in largish blocks (as opposed to occasionally altering a single value) it might slow it down, and you might be better putting it as an on_click on a button to do a block update.