How to color range based on XLOOKUP

Hello everyone,

i need some help with vba because i dont know much about it.

I have a large data range (50x 50 cells). I am using the XLOOKUP to find the average and Stdev of a range within that data. I also used XLOOKUP and CELL function to find the first and last cell of my range.

Can anyone help me with a vba code that can automatically select that range and change the color of the range cells?

i would like to use this vba code on all my sheets, not just one.

here is the XLOOKUP function i am using in case it helpfull:
=(XLOOKUP(AH59,A1:A50,XLOOKUP(AH62,B51:AY51,B1:AY50,"not Found",-1,1),"Not Found",-1,1)):(XLOOKUP(AH60,A1:A50,XLOOKUP(AH63,B51:AY51,B1:AY50,"not Found",-1,1),"Not Found",1,1))

Thank you for the help!

I wrote a quick program(?) to do this but it takes forever to complete, very inefficient

Please Login or Register  to view this content.

each range is 2500 cells, total is like 12.5E+06 comparisons.

there must be another way to do this.

Macro code.
Conditional formatting is to be removed to color cells

Please Login or Register  to view this content.

For starters here's 1 that does it in 6.9sec.

Please Login or Register  to view this content.

@Ksenofon111

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. As you are new, I have done it for you today.)