Hi experts,
I need a VBA to automatically change the font color of the 3 highest values in a row..these rows may include rows in a pivot table..and i would like that i'll be able to change the number of values to highlight from time to time (say 5 highest values, 6 highest values etc.)
I tried using conditional formatting with this formula =A1>=LARGE($A1:$T1,3) but the reference cells keeps on changing when a pivot table updates.
Please help
I'd use with a worksheet change or selection change event. This code goes directly on the sheet it applies to.
This code is for Rows 1 to 25. Adjust range to fit your need.
Please Login or Register to view this content.
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
is there a way to speed up the macro?
Please take note that the pivot tables are connected to one pivot table who is refreshed via worksheet activate.
Then I'd call this subroutine from within that one, near the end, instead of using another event.
Please Login or Register to view this content.
Last edited by daffodil11; 04-03-2015 at 05:19 PM.
Is there a way to have the macro read only until the last row with data?
What do you mean read only? Do you wish to only add new formatting to the last row?
i wish the macro to run from a specific starting row and end on the last row with the data..
so basically we'll start on row 7 and then the last row will vary every time the pivot table is updated.
so the macro won't be specific to a certain number of rows e.g. For i = 7 to 25
Oh, of course.
First we have to know what column that last row will appear on. Let's pretend you will always have data in Column D.
That last part start at D, counts the rows in the worksheets (1.04 million) and then moves upwards until it hits the first occupied cell.Please Login or Register to view this content.
So if you last occupied cell is D23, this will run for row 7 to 23. It will dynamically update itself each time.
thank you so much sir.. appreciate your help
No problem. Also might help if you slide the delete up front, so that old formatting is removed if less data is present.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks