Macro for Conditional Formatting

**Humanist** · 10-26-2010, 12:06 PM

Hello. I need to make a macro that will apply several conditional formatting items to whatever area I have selected. The formulas are as follows assuming that I selected large chunks of columns AR through to AV:

=IF(SUM($AR2:$AV2)>0,1,0)
=IF((COUNTIF($AR2:$AV2,"C")>0),1,0)
=IF(SUM($AR2:$AV2)<1,1,0)

Each one needs to set the cells to a custom colour... I assume that I can tweak this part of the macro myself once I see the logic.

Thanks - I'm really stuck.

**Paul** · 10-26-2010, 02:08 PM

Hi Humanist, have you tried just recording a macro while you select a range and apply those conditonal formatting formulas? It will show you the exact code layout you need to apply it to other ranges. (Note: CF coding and architecture is different between Excel 2003 and 2007/2010.)

**Humanist** · 10-26-2010, 02:43 PM

I did try recording, but the macro seems to record and define the actual cells I selected. I guess I was hoping that the macro would allow me to work with any range. Does that make sense?

**ChemistB** · 10-26-2010, 04:50 PM

If there are only 3 conditions, why not use the native Conditional Formatting.
Note: With CF, you do not need the IF statements, i.e. select your ranges starting with row 2 and
Condition 1 = SUM($AR2:$AV2)>0
Condition 2 = COUNTIF($AR2:$AV2,"C")>0)
Condition 3 = SUM($AR2:$AV2)<1

Does that work for you?

**Humanist** · 10-26-2010, 06:11 PM

Originally Posted by ChemistB

If there are only 3 conditions, why not use the native Conditional Formatting.
Note: With CF, you do not need the IF statements, i.e. select your ranges starting with row 2 and
Condition 1 = SUM($AR2:$AV2)>0
Condition 2 = COUNTIF($AR2:$AV2,"C")>0)
Condition 3 = SUM($AR2:$AV2)<1

Does that work for you?

The problem is that these formulas evaluate one school week for each student, turning data red if no result is achieved (3) and yellow if no result is achieved but I remembered to call home (2). So every week I need to define 5 new columns and re-apply the same fomulas, but in a different set of conditonals. (There may soon be a 4th)

If I understand correctly, your suggestion would assess all colums at once, but I need to assess 40 sets of 5 seperately...and to be gradulally adding sets (up to 40) week-by-week.

I hope I am being clear. If not, I can upload what I'm doing....

**Humanist** · 10-26-2010, 07:54 PM

Really, I guess I just need to know if there is something I can substitute into this line:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=SUM($J2:$N2)<1"

To make $J and $N dynamic based on my selection. The "2" will always be true.

and to change
Range("E2:I5").Select
so that the entire range is taken from what was already selected and acted upon in the previous conditonal.

**ChemistB** · 10-27-2010, 08:25 AM

I'm not getting a visual. Best to upload an example.

**Humanist** · 10-27-2010, 09:13 AM

Attached please find a small part of the spreadsheet in question. The real one has much more data on the right and a LOT more students.

As you may see, each school day is grouped into weeks. Into each day I write the number of hours the student was in class or a "C" if I called the student.

Each week has 3 conditions:

No format if a number is present.
Yellow if no numbers are present but a C is present (They didn't show, but at least I took action)
Red if there is no number and no C in that week.

The idea is that red bars (each being a week of no attendance for a single student) will stand out as problems to be solved. Yellow bars will alert me to a problem (no attendance for that week) but where action is being taken in the form of a phone call home. Each week needs to be analyzed seperately.... although I am considering adding another layer where 3 weeks in a row of non-attendance results in a brighter red.

Everytime I define 5 new columns as the new school week, it is a big pain to add the 3 conditons over and over again. That is why I'd like a macro or some other solution.

I hope this is clear now. I teach in a weird school, so the set-up is not something you'd expect from normal classes.