Conditional formatting from separate pages

There's no reason why you can't use named ranges in your conditional formatting formulas, in fact when you need to reference another sheet that's the recommended method.

What formulas did you try?

If you select your whole range, i.e. sheet1!A1:AA200 with A1 the active cell (i.e. select A1 first) then just use this formula for MALE

=COUNTIF(MALE,A1)

and similar for FEMALE

daddylonglegs,

Thanks for your response.

If you select your whole range, i.e. Sheet1!A1:AA200 with A1 the active cell (i.e. select A1 first) then just use this formula for MALE

=COUNTIF(MALE,A1)

and similar for FEMALE

That was the first code I tried...and it worked, except for one thing.....

Each BLANK cell in range A1:AA200 was formatted RED, since Condition 1 was being met...(due to one or more BLANK cells in MALE).


I need cells to retain their original format if no match exists in MALE or FEMALE...or if there is no name in a particular cell (i.e.: Sheet1!A1 is blank).


Charles,

Your VBA looks like something I can work with. I think VBA is the way to go, anyhow....since I want the formatting to work in the event that any conditional formatting is inadvertently wiped out by the user.


Thank you both, for your excellent replies.

Originally Posted by HuskerBronco

Each BLANK cell in range A1:AA200 was formatted RED, since Condition 1 was being met...(due to one or more BLANK cells in MALE).

If cells are completely blank then the formula I suggested shouldn't result in them being formatted. If your cells contain "formula blanks", i.e. "" returned by a formula then you can avoid formatting with a simple tweak to the above, i.e.

=COUNTIF(Male,A1)*(A1<>"")

Charles....

I was very confused after reading your latest post. So, I studied the code to see if I could understand what you were getting at...and I think I do. Tell me if I've got this right....

Consider the name currently in Sheet1!A1.

The first thing that the code does...is look into the MALE range...checking for any name that matches the name in A1. If it finds a match, the cell is formatted green. If no match is found, then the code looks at the names in the FEMALE range...to see if ANY of them match the name in cell A1. If a match is found...the cell is formatted red. If no match is found in either range, the cell retains it original format.

If I've got that right, then it becomes clear that the code is working in reverse of what I need. Since names will be constantly added to, and deleted from, both the MALE and FEMALE range...I need code that will look at cell Sheet1!A1 FIRST...and THEN check each range for a match...formatting the cells accordingly if a match is found. In addition to that, the name currently occupying Sheet1!A1 may be sorted to a different cell during the alphabetization process. If that happens, the cell would have to be reformatted to properly identify the *** of the NEW occupant of Sheet1!A1, as well as formatting the new location of Sheet1!A1's former occupant.

This would allow me to add or delete names from either range, AND/OR sort Sheet1....and still have each cell properly formatted.

Can this be done....?? Or should I try a different approach...??

Hello Charles....

Sorry it took me so long to get back to you.
I attached your latest code to the end of my SORT macro...and it worked exactly as planned.

In your last reply, you stated:

If neither is found the sheet1 name will remain without color.(unless you added a name on sheet1 that had the cell fromatted for either cat. and that name was not in sheet2 the color will remain)

This was exactly the case...and something that had to be corrected, since the names in the cells on Sheet 1 were constantly being changed each time the SORT macro was run.

My simple solution was to add the following code AHEAD of your code...to clear the formatting prior to your code's execution.

Please Login or Register  to view this content.

The unfortunate side effect of all of this...??? ...My SORT macro now takes longer to run...(about 10-12 seconds).
Since I have ScreenUpdating set to FALSE on the macro...I now need to figure out how to attach a STATUS BAR to the macro so that the user doesn't feel like nothing is happening, or that the program has hung.

Going to get to work on that, right now.

Thanks a bunch for your help with this.
I've learned a great deal more about VBA from this exercise.

Hello, again, Charles....

In your last reply, you stated the following:

I don't think it's necessary for the code to set Font style to Regular.

I failed to mention that my SORT macro sets Sheet1 matches to bold.
(Actually, I modified YOUR code to do so...before I added it to my SORT macro).
Therefore, I needed to reset the font prior to running your new code.

I considered leaving the font 'Regular' throughout the sort process...to speed up the macro.
After removing that code...and removing code to reset the font to 'Regular'...
the macro execution time wasn't noticeably diminished.

Perhaps I could reply with the exact code I am using in my workbook...and you might see other ways that I might speed up this macro. I am sure, due to my inexperience with VBA, that some of my code is redundant...or even completely unnecessary.

Would you be interested in glancing at this code, and offering your advice??

I thank you for the help that you have given me, so far. The educational value of this forum is "off the charts". I have yet to post a question that couldn't be answered to my satisfaction. I can't thank you enough for the time and thoughtfulness that you put into this forum.

My thanks go out to the following moderators and registered users, as well:

VBA Noob - kraljb - duane - Bryan Hessey - Special-K - JR@SGC - tony h - jtp - davesexcel - Carim - MSP77079 - vane0326 - Dav - Clayton Lock - rylo - dominicb - Norie - downforce - Leith Ross - Mikerickson - ChemistB - Aladin Akyurak - and last but not least...daddylonglegs.