[sims] Macros for excel - reporting - I need some help.

[CatherineMay]

I have a report which uses colour and filters on numbers and I want to change the the filter on the numbers. I want to change the numbers to the following:

 

Green =10 to =15

Amber =16 to =23

Red =24 to =5000

 

I have tried to follow the the manual to created a macro, however, every time I run the report I get a error message out of code.

 

The sims report has forename, surname, reg, authorised absence, unauthorised absence and a blank column named Sessions Missed: the macro hides the two absence columns and shows the sum of the absence columns in the Sessions Missed.

 

I've attached the excel file for the old report and wondered whether anybody could look at it and alter it for me. I know it is cheeky.

Attendance Profile Report.xlsx

[Steve21]

I feel you're trying to overcomplicate this (Unless I've misunderstood) There are no macro's on that sheet. It's simple condition formatting.

 

Highlight the cells, click conditional formatting, Manage rules, Select one (eg Green), and edit the rule. That's all you need to do I think.

 

Attendance%20Profile%20Report(1).xlsx

 

Like that? (Or do you need the macro, to add into the report? Can't you use the excel pre-formatted sheet as a template?)

 

Steve

Edited January 17, 2012 by Steve21

[vikpaw]

If you do use conditional formatting, you could start with a blank sheet, then record a macro of you applying the rules, this will then give you the correct code.

You might need to save the file as a .xlsm file so we can see the macro if there's one in there already, or are you doing it from within sims so the macro is in the template file..

[CatherineMay]

Thanks, the template is in SIMS, I can get the colours with no problem, however, the formula for the add the two columns give me a error message: Error 9 : Subscript out of range, and the cell shows: =SUM(RC[-2]:RC[-1]) instead the value of the formula. This is the first time I have used macro, really don't know I am doing!

[CatherineMay]

I can't attach the file, would you send me an email address?

[Achandler]

So you just want the boxes to the following:

 

24 to 50 to go Red

16 to 23 to go Amber

10 to 15 to go Green

 

Automatically ofcourse?

[CatherineMay]

Yes automatically - but the red is 24 to 5000

[Achandler]

O Ok, thats definitely just formatting, I'll change it and then pm you the file (assuming you can PM files about).

 

Edit: I assumed wrong you cannot PM files, or i can't see it because the little icon has changed. Sent you a PM anyway

Edited January 17, 2012 by Achandler