VBA Code to set fill color of cells (criteria based)

[Erudite.Warrior]

Hello, first post. What an awesome resource! I had no idea there were so many other "Excel Geeks" like me in the world!

Here is my problem. I have exceeded the limits of conditional formatting. I want Excel to automatically set a fill color for cells based upon specific criteria. The criteria is the same that inputs the letters in the cells (T, A, B, C, D). I belive there is a way to do this using VBA, I just do not know how. Your help would be greatly appreciated.

The sheet below is a VERY SMALL example. In the project I am working on the month columns go out to 2020 and there are several more "Items" to be evaluated.

I have no experience wrting VBA. But have very dated experience writing code (15 years ago). The top spreadsheet is what I have now. The bottom is what I want it to look like (included for clarity).

Thanks in advance for your help!

Example Problem.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Person	(T) 90-Days	(A) 30-Days	(B) 30-Days	( C ) 60-Days	(D) 30-Days	Jan-06	Feb-06	Mar-06	Apr-06	May-06	Jun-06	Jul-06	Aug-06
2	John	03-Jan-06	01-May-06	01-Apr-06	17-Jul-06	01-Dec-06	T	T	T	B	A			C
3	Pete	16-May-06	01-Sep-06	01-Apr-06	12-Feb-06	01-Nov-06		C	C	B		T	T
4	Mike	17-Jun-06	01-Nov-06	01-Feb-06	01-Apr-06	01-Jan-06	D	B		C	C		T	T
5
6
7	Person	(T) 90-Days	(A) 30-Days	(B) 30-Days	( C ) 60-Days	(D) 30-Days	Jan-06	Feb-06	Mar-06	Apr-06	May-06	Jun-06	Jul-06	Aug-06
8	John	03-Jan-06	01-May-06	01-Apr-06	17-Jul-06	01-Dec-06	T	T	T	B	A			C
9	Pete	16-May-06	01-Sep-06	01-Apr-06	12-Feb-06	01-Nov-06		C	C	B		T	T
10	Mike	17-Jun-06	01-Nov-06	01-Feb-06	01-Apr-06	01-Jan-06	D	B		C	C		T	T
Example (3)

 

[Joe Was]

Private Sub Worksheet_Change(ByVal Target As Range)
'Sheet module code like: Sheet1.

If Target.Value = "T" Then Target.Interior.ColorIndex = 35

If Target.Value = "" Then Target.Interior.ColorIndex = xlNone
End Sub


Just copy the "If" statement down as many times as you need for your conditions in this Event, then change the Trigger value [Case Sensitive!] and its resulting color!

To get the color index record a macro for the color you want.

This code is automatic.

 

[Erudite.Warrior]

Thanks for the code. This gets me close but does not quite solve the problem. The code you provided applies the appropiate fill color ONLY when I type a "T", "A", "B", "C", or "D". It does not apply the formatting when the FORMULA RESULT is T, A, B, C, or D. The code should apply the fill color based upon the formula result AND apply the formatting retroactively. My error for not explicitly stating this.

As a side note. The code crashes when copying or deleting multiple cells that include T's, A's, B's, C's or D's. Also, the formatting is not applied retroactively (even when forcing a recalculation of the spreadsheet).

Any more ideas? Thanks!

 

[Joe Was]

The Target in this case can only be one cell.

To get around this Use a loop that runs on the sheet's Change and Calculate Events!


Private Sub Worksheet_Calculate()
'Sheet module code, like: Sheet1.
myTest
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Sheet module code, like: Sheet1.
myTest
End Sub

Private Sub myTest()
'Sheet module code, like: Sheet1.

Dim myRng As Range

Set myRng = ActiveSheet.Range("A1: F33")

For Each Cell In myRng
If Cell.Value = "T" Then Cell.Interior.ColorIndex = 35

If Cell.Value = "" Then Cell.Interior.ColorIndex = xlNone
Next Cell
End Sub

 

[Erudite.Warrior]

Joe Was,

Thank you SO MUCH!! You are definitely an Excel Jedi Master. I am humbled by your knowledge. I wish I could buy a beer (or three) to thank you!