VBA code to hightlight a data range on 140 worksheets

[seansr]

I have a Workbook with 143 worksheets on it
2 worksheets are for reference ("contract Look Up" "All contracts Feb 2021" " Key" ( more of these could be added or names changed) (if it helps these have tab colours Purple from the standard row)

all of the other worksheets have no tab colour at the moment
Each worksheet is named with a naming convention AAANNN

Column L is the Next bill date
I want to be able to highlight all dates that fall into a month

I normally do conditional formatting say For Aug (from 44409 to 44439) and fill and change the font to stand out, then use the format painter, but it takes ages
I was wondering if anyone had any idea to use some code to run a macro

I could change the from and to each month in the code

I can't share the workbook due to the sensitive nature

 

[vletm]

seansr
Based Your I can't share the workbook due to the sensitive nature
You should able to create a sample Excel file, which has same layouts and needed sheets with sample data as well as expected results.

 

[seansr]

Customer Contracts for Chandoo.xlsm

Shared with Dropbox

www.dropbox.com

Above is a link to the file in Dropbox, its too big to upload

So let me explain what I do:
Each month I filter, using KUTOOLS super filter the relevant month, So now I am looking at Filtering month = 8
then I apply conditional formatting to all in column L for the next billing month if between 44409 -44439
Then I go through every sheet and if that sheet and if that sheet contains and lines with the month (I am looking for) on I change the tab colour
then using KUTools I sort by Tab colour and bring all the coloured tabs to the start of the workbook
Then I audit the sheet against actual
then it's reviewed in a meeting
then the process is repeated
Clear the conditional formatting, Clear the Filter
Start again

I am sure there is a better way but this is how I work at the moment

I would be very grateful if anyone can assist

Normally this workbook has queries against each table(worksheet)
and I run a power query based on the month I am looking at and at the end of the period a Copy of that is saved and maintained for auditing
in order to make it smaller, the power query has been removed and all queries deleted

 

[Marc L]

Hi,​

​

if you want to avoid any conditional formatting you can just loop on sheets an use a filter to color directly the cells matching the month …​

​

 

[seansr]

How would I do that

 

[Marc L]

Like any Excel beginner operating manually : just start on a sheet, filter the data and once the result matches the dates range,​

cancel the filter, activate the Macro Recorder and redo the same operations then post the generated code​

in order someone can optimize it for all sheets.​

And do not forget to well elaborate from where the dates range comes as here this is not some mind readers forum​

so I never try to guess anything …​

​

 

[p45cal]

1. This is NOT going to answer your question.
It looks like you're doing all this highlighting to bring attention to sheets and rows of your 140 odd sheets. When reviewing, that's going to involve a lot of clicking and scrolling, so what about if you had a routine which brought all those rows into one sheet? The Customer Number's in column C which identifies which sheet a row comes from. So…
2. In the attached is a Power Query query which:

• Looks at your big data file (you'll have to tweak the code to look at the correct file - but you seem to know PQ so you will know how to do that).
• Uses the date at cell A1 of the Control sheet to determine Month and Year (it can be any date within the month concerned).
• When the result table on Sheet1 is refreshed it takes a few seconds to get the data.

Note that the M-code identifies the table names to be used in your workbook as those starting with 3 non-numeric characters followed by at least one numeric digit after. There is no need to have a query for each and every table.

Would this be a route that might be worth taking further?

[This takes some time because every time the date is changed a refresh needs to be done, so I'm thinking it might be better to get all the data in PQ and load it to pivot table and do the date filtering there, so no refreshing/fetching of data needed unless you know the source data has changed. I'll explore thast too.]

 

[p45cal]

A pivot table version with a monthly timeline.