Excel Macro: Changing Color of Cell

[kevinknight09]

I am anything but an Excel or VB programmer. I knew I needed a macro
and using some others I found on the web I created the following:

==============================================
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 = "C45, E45, H45, I45, M45, B47"

' 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 C45,E45,H45,M45,B47 are greater than nil...
For Each Cell In Range("C45, E45, H45, M45, B47")
If Cell > "" Then

' Make the background color of the cell the 3rd color on the
' current palette.
Cell.Interior.ColorIndex = 3

Else
' Otherwise, set the background to none (default).
Cell.Interior.ColorIndex = 0

End If
Next Cell
===========================================

How is it possible to have the background color in cell A44 change to
Cell.Interior.ColorIndex = 3 instead of cell C45, E45, H45, M45, or
B47? I thought it would be as simple as changing line

Cell.Interior.ColorIndex = 3

to

Cell.Interior.ColorIndex("A44") = 3

but this causes the macro to crash and the debugger comes up. Again,
any assistance would be greatly appreciated.

Thank you all in advance.
Kevin Knight

 

[Bernie Deitrick]

Kevin,

Range("A44").Interior.ColorIndex = 3

BUT (and this is a big BUT) you could also use the built-in functionality of Conditional
Formattting. Much easier in the long run, especially if cells, columns, or rows can be inserted,
deleted, or moved. Far better, given the simple conditional used.

HTH,
Bernie
MS Excel MVP

 

[kevinknight09]

Thank you!!!!

 

[kevinknight09]

Would you be willing to share the Conditional Formatting that you
referenced? Again I apologize for my lack of Excel and VB knowledge.
Thank you!

 

[Bernie Deitrick]

Kevin,

Select cell A44, for example. Then from the main Excel menu, choose Format / Conditional
Formatting.... and choose "Cell Value is.." and then one of the conditionals, and select your
limit or condition. Then set the format that you want by clicking the format button. Look in help
for examples.

HTH,
Bernie
MS Excel MVP

 

[kevinknight09]

Again, thank you!

 

[Guest]

I am not sure my question is relevant to your discussion. I am new here. I
want a font color to follow the numbers and or text when i reference that
cell on another spreadsheet. How do i do that?

Kevin,

Select cell A44, for example. Then from the main Excel menu, choose Format / Conditional
Formatting.... and choose "Cell Value is.." and then one of the conditionals, and select your
limit or condition. Then set the format that you want by clicking the format button. Look in help
for examples.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

squaglia,

When you create the reference, instead of typing in a formula, go to the cell that you want to
reference, copy it, then go to the cell where you want the cell value and formatting, pastespecial
formats, then pastespecial paste link. Of course, if the formatting of the original cell changes,
the format of the linked cell won't be updated.

HTH,
Bernie
MS Excel MVP

I am not sure my question is relevant to your discussion. I am new here. I
want a font color to follow the numbers and or text when i reference that
cell on another spreadsheet. How do i do that?