Conditional formatting from separate pages

Is there a formula I can enter into the conditional formula palette that will allow me to format cells on one sheet based on a match found on a second sheet? ....or do I have to use VBA to do this??

Here, specifically, is what I am trying to do:

Sheet1 A1:AA200 contains a list of names.
Sheet2 A1:A200 consists of a Range named MALE.
Sheet2 B1:B200 consists of a Range named FEMALE.

Consider, first...that some of the cells in BOTH named ranges may be blank.

I want to format all of the cells in Sheet1 A1:AA200 to turn green if a match is found in the Range named MALE....and turn the cells red if a match is found in the Range named FEMALE.

When I try to use the Range names in my formula...I get various errors.

Please advise me if I need to use VBA to accomplish this. Writing VBA to do this should be relatively simple...but I would appreciate some sample code to use as a model, since my VBA coding skills are still in their infancy.

Thanks, in advance, for any help you can give me.

Hi,

This a a simple code. Paste to a module.

Sub test()
Application.ScreenUpdating = False
Dim myname As String
Dim cel As Range, mcell As Range, fcell As Range, male As Range, female As Range
For Each cell In Range("A1").CurrentRegion
    If cell.Text <> "" Then
        myname = cell.Text
            With Sheets("Sheet2")
            For Each mcell In Sheets("Sheet2").Range("male")
                If mcell.Text = myname Then
                    cell.Interior.ColorIndex = 4
                End If
            Next mcell
            For Each fcell In Sheets("Sheet2").Range("female")
                If fcell.Text = myname Then
                    cell.Interior.ColorIndex = 3
                End If
            Next fcell
            End With
    End If
        
Next cell
End Sub

Thanks for the feedback.

Hey guys....I hope you are still there...!!

You, first, Charles....

I Rt-clicked the sheet tab for Sheet1 one of my spreadsheet and entered your VBA. (I added the missing "L" in this line of your code, first):

Dim cel As Range, mcell As Range, fcell As Range, male As Range, female As Range

Next, I ran the VBA...and everything seemed to work flawlessly.
Sheet1 quickly became a grid of GREEN and RED cells...(with a few unformatted cells for names not found in either list).

I wanted to test the VBA by entering a few names, and deleting a few names from both MALE and FEMALE ranges on Sheet2. After doing so, I clicked back to Sheet1 to see if the cell formatting had changed. It had NOT!!! The cells were the SAME color as they were after I initially ran the Sub. So, I went to Sheet2, and deleted the ENTIRE list of names in the MALE range. I expected that there would be NO FORMATTING in the cells containing MALE names....but there was!!! In fact, the formatting had not changed in ANY of the cells on Sheet1.

Also, I have a macro that sorts Sheet1 alphabetically. After running this macro, all cells retained the formatting from the original sortation. (Meaning...if cell A1 had been formatted GREEN, it remained GREEN even if the name in that cell had changed to a FEMALE name.

At this point, I figured that I must be doing something wrong. I began to assume that I needed some kind of event to trigger this VBA, but I had no clue how to trigger it. In an attempt to get Sheet1 freshly formatted, I opened the VBE and triggered the VBA from there.

This time.... ....I got an error message:

Run-time error '1004':
Application-defined or object-defined error

What am I doing wrong, here.....???


daddylonglegs,

Yes, some of the cells in the MALE and FEMALE ranges are "formula blanks".
As I mentioned earlier...I initially had formulas identical to yours as Condition 1 and Condition 2 of my conditional formatting. This meant that any BLANK cells in the conditionally formatted range would turn RED...because they were meeting Condition 1.

I changed the formulas in the manner that you suggested:

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

And when I clicked OK....NOT ONE of the cells on Sheet1 was formatted...!!!
And furthermore, when I tried to return to the original formulas...(get this...!!!)...there was STILL NO FORMATTING on Sheet1. And now, for some reason or other...I can't even get those OLD conditional formats to work.

What could possibly be the problem...???

The color index on sheet 1 will not change even if the names within the list on sheet1 changes . You set the code to say if the name in either ranges are not found then the color index changes to what ever you say.

Hi,


I don't think it's necessary for the code to set Font style to Regular.
The below code may be a little faster.

Range("A1:AA200").Interior.ColorIndex = xlNone