Conditional Formatting by VBA

[AndyRoo119]

Hi All,
I asked, on the Forum, for help regarding colouring 'birthday' cells based on the Month of the year.
The answer, and solution, was excellent (even though I'm not to sure how it works).
Now, I've been asked to sort the same birthday list by the Age of the person.
Unfortunately, the current Conditional Formatting by Months does not work and colours the cells randomly.

My thoughts are to change the colours of the cells using VBA.
Colour the cells when sorted by Months, when sorted by Age, just remove the formatting and colour the cells light green (for example).

I've attached a sample couple of Worksheets to show what happens - (TrialSheet) and what I've been trying to do with VBA (DevSheet).

Any advice would be appreciated.

Thanks.

[AliGW]

What do you want it to look like when sorted by age??? It probably doesn't need VBA.

[frabulator]

I agree with AliGW. It sounds like you would not really need VBA for this.

It sounds like your issue is arising from not selecting all the data before you sort.

Please see this image for reference.

Select ALL of your data (including the hidden cells), then go to Data> Sort> Sort By (Column L) - Sort On (Cell Values) - Order (Smallest to Largest)> OK

Capture02.JPG

---

You also have the option to record your own macros so you can see how they work, just in case you did not know about this. This tool is under Developer> Record.

Recording the example above yielded this result.

Please Login or Register  to view this content.

[AndyRoo119]

Thanks AliGW, for looking at my problem.

When sorted by Age, I just want all the cells containing Dates and Names to be one colour, or even no colour come to that.
I can clear the Dates and Names using VBA when sorting by Age by using something like - "(Range("G3:H35").Interior.ColorIndex = 0"
Getting the formatting back again when sorting by Month using "=ISODD(SUMPRODUCT(1/COUNTIF($E$3:$E3,$E$3:$E3)))" and "=ISEVEN(SUMPRODUCT(1/COUNTIF($E$3:$E3,$E$3:$E3)))" in conditional formatting, is my issue.

Hope i'm making sense?

[AndyRoo119]

Thanks frabulator,

I know about the recording of macros but using that method puts the blank rows, from 27 to 35, at the top of the sorted list.
How can I convert the line - .SetRange Range("E3:J35") to only include down to line 26?
i've used something like - "lRowG = sht.Cells(sht.Rows.Count, 7).End(xlUp).Row" to find the last row with data in col G, but cannot work out how to put the same
on the ".SetRange Range("E3:J35")" line in the macro.

Thanks

[AliGW]

How about this?

=AND($G$1="Sorted by: Month",ISEVEN(SUMPRODUCT(1/COUNTIF($E$3:$E3,$E$3:$E3))))

=AND($G$1="Sorted by: Month",ISODD(SUMPRODUCT(1/COUNTIF($E$3:$E3,$E$3:$E3))))

Click your sorting buttons to see the effect.

[AndyRoo119]

Thanks AliGW,

Absolutely ideal!

Many, and I mean many, years ago, I used to use program using Fortran77 at my work.
Unfortunately I'm physically not able to do much at all now, so I thought, let's try and keep the
"little grey cells" going and look at Excel (as it came with my comp).
I realise now, my lack of logic, but thank you, and all like you who help unconditionally.
It is very much appreciated.

Thanks once again.

Andrew

(p.s. I would never have been able to work out the "=AND($G$1="Sorted by: Month" bit.)

[AliGW]

It's certainly great for the little grey cells here - that's what I come her for, and for the pleasure helping others gives.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who helped.

PS I come from Sawley.