Is it possible to execute code for the purpose of formatting any time a cell is updated For instance, if I want 8 different formats for a cell given certain conditions Is the only way to do this with a timer then just fire the code ever minute or so
Executing code off of a cell update
Hot Topic
- Allowing the user to pick a cell in excel
- Validation list macro
- Creating Shortcut in Access 2003
- Runtime error '13' - type mismatch problem! Again...
- Error hanlder that does not end macro
- Page Setup in Excel 2003 using VBA
- Prepare a list in excel for printing
- Windows media files have changes to Nero
- making the previous ws active in excel
- Range
Microsoft ISV
- Highlighting cells within a worksheet
- VBA Excel - Using FindNext
- Error setting range to hidden in Excel 2003, not in 2000
- Combining Control Sources for Use in Reports
- Address Book Issues
- Track changes in Excel
- Place a CR in normal Text Box via vba
- [Excel 2000 + VBA] How can I check that cell colour in was changed in Worksheet_SelectionChange
- saving data in a userform without saving the workbook
- DLookup help?
Executing code off of a cell update
helen369331
"56 colors, eh I assume it's 32 system colors and 24 custom colors "
No, these are Excel's own colors, independent of system colors. You can change any of the 56 to any RGB you want, but you can never have more than 56 at one time. Excel 2007 is no longer constrained in this way.
Here's some further information about Excel's color palette:
http://www.mvps.org/dmcritchie/excel/colors.htm
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
hashbrown
Yes, absolutely, and there's no reason to involve a Timer in this. Add this code to the relevant Sheet module of your workbook:
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Value
Case "red"
Target.Font.Color = RGB(255, 0, 0)
Case "blue"
Target.Font.Color = RGB(0, 0, 255)
Case "green"
Target.Font.Color = RGB(0, 255, 0)
Case Else
Target.Font.Color = RGB(128, 128, 128)
End Select
End Sub
Basically, whenever the contents of a cell change, this event handler will be called. Target will contain the Range object corresponding to the cell whose value changed. Just test Target.Address to make sure it's within the range of cells you wish to auto-format, then set their formats as you please.
Mohan1
Keep in mind that you may not get the RGB you are asking for. Excel's color palette is limited to 56 colors, and if none of them matches the RGB in thee code, Excel will use whatever palette item it thinks is the closest match.
Of course, green, red, and blue are part of the default palette.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
Bogey1
Jon, you're absolutely right. I wrote the demo code to change the color of cells simply because I thought that was the easiest and most effective way to demonstrate the concept.
56 colors, eh I assume it's 32 system colors and 24 custom colors