MIS Systems Thread, Macros for excel - reporting - I need some help. in Technical; I have a report which uses colour and filters on numbers and I want to change the the filter on ...
16th January 2012, 09:13 PM #1
Macros for excel - reporting - I need some help.
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.
17th January 2012, 08:26 AM #2
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.
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?)
Last edited by Steve21; 17th January 2012 at 08:32 AM.
17th January 2012, 08:56 AM #3
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..
17th January 2012, 09:16 AM #4
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!
17th January 2012, 09:36 AM #5
I can't attach the file, would you send me an email address?
17th January 2012, 09:48 AM #6
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
17th January 2012, 09:52 AM #7
Yes automatically - but the red is 24 to 5000
17th January 2012, 09:53 AM #8
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
Last edited by Achandler; 17th January 2012 at 09:59 AM.
By jaky in forum MIS Systems
Last Post: 6th January 2012, 02:17 PM
By Inbir316 in forum Hardware
Last Post: 4th February 2009, 02:14 PM
By martin88 in forum Windows
Last Post: 7th March 2008, 02:28 PM
By SYSMAN_MK in forum Hardware
Last Post: 6th March 2008, 02:17 PM
By contink in forum General Chat
Last Post: 14th March 2007, 03:47 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)