match with conditional format in excel 2003Solved/Closed

Question

Hello Internet, 

I am in a bind here as I have exhausted my excel knowledge on this problem. I am looking for a way to highlight (in red) a cell which contains text that does not match text in another. 

My spreadsheet has a layout as thus, A row of names is located on row 5 and extends all the way to column IV (as far as excel will allow). I have named this field "names".  

These names are repeated in a grid, named "Grid" on another sheet, the whole grid is named, including the headers and the descriptions down the side.  

I have used this code :  =NOT(ISERROR(MATCH(C$5,GRID,0))) in each cell of the NAMES field 

and have also used various forms of a LOOKUP in some attempt at making things work but to no avail. 

Imagine if you will that my 200+ names are forever being renamed or removed altogether from the grid and so I have a need to visually differentiate between names which are being matched to the grid and names which need to be altered on the "NAMES" field in order to for both data sources to relate. Here is the clincher though, I cannot simply point 1 name to another as the "GRID" is actually used to monitor where a person is working week on week and thus, names are moved around within the grid! 

What I need, had excel learned to speak English, is a format which says: 

"Match the text (name) in this cell to any cell within this "GRID", if there isn't a match then turn the cell in "NAMES" red. If there is a match, don't worry". 

I hope I have made myself clear, and that the code is yet another thing I can learn (as opposed to something stupid I didn't think of) 


Configuration: Windows XP / Internet Explorer 8.0

rizvisa1 · Accepted Answer

Well I am sure there is other way to do it too. This is a proposed solution for your two issues
1. Indicate which name is not present in the GRID
2. If name is present, location of that person

I am proposing two functions. 
A. One function isPresent(range, string) returns true or false depending if the name was found in the GRID or not
B. The other function PresentAt(range, range, string) returns a string. This string will either contain the location name or "Not Available" if the name is not found

Step1

1. Create a new Named range "LOCATION". This is the name of various location you have on your sheet2, row 2. This range should start from the same column where the GRID name range starts and should end at the same column where the GRID named range ends


2. Open VBE by pressing ATL + F11 and then inserting a new module

3. Paste the code below ( after the instructions)

4, Once the code has been pasted. Select the row containing the names on sheet1

5 Go to Conditional formatting and enter this formula
=NOT(OR(A$5="",ispresent(GRID,A$5)))
NOTE : A$5 is based on your sample book, In that the names were on ROW 5. Change it to what ever rows they actually are

6 Go to format under conditional formatting and pick the color of your choice

Here is the code
Function isPresent(NameGrid As Range, lookup As String) As Boolean
'NameGRID: the grid that contain names and location of person
'lookup  : name that is to be searched in GRID

Dim temp As String

    ' to upper case as we would be comparing the names latter and
    ' want to take out any issues due to upper or lower cases of
    ' alphabets
    temp = UCase(lookup)

    'default the name is not present
    isPresent = False
    
    ' looping thru each cells in GRID
    For Each cell In NameGrid
    
        'comparing names
        If UCase(cell.Value) = temp Then
            
            'name has been matced
            isPresent = True
            Exit Function
        End If
    Next
    
End Function

Function PresentAt(LocationGrid As Range, NameGrid As Range, lookup As String) As String
'LocationGrid: the named ranged that has the locations only.
'NameGRID: the grid that contain names and location of person
'lookup  : name that is to be searched in GRID

Dim temp As String
Dim lItem As Long
Dim Locations As Long
    
    temp = UCase(lookup)
    
    'default
    PresentAt = "Not Available"
    lItem = 0 ' array pointer
    Locations = LocationGrid.Cells.Count 'number of cells in the locations grid
    
    For Each cell In NameGrid
        lItem = lItem + 1
        If UCase(cell.Value) = temp Then
            PresentAt = LocationGrid.Cells(1, lItem)
            Exit Function
        End If
        
        If lItem = Locations Then lItem = 0
    Next
End Function

rizvisa1 · Answer

Could you please post a sample book at some share site like  https://authentification.site and post the link here.

ConfusedPeopleMover · Answer

The link takes me to a "forbidden server" message. I tried to post an upload with this very site when I first posted but I got the same thing when I tried to upload before. I shall try using IE rather than Google Chrome to get to the site but if that fails, is there any other option? I could make my description clearer?

rizvisa1 · Answer

Strange, I was able to go. You have been very clear. It is just that i find picture speaks louder than the words. Could you mail me the file at "rizvisa at hotmail dot com" . Hopefully no virus, no Trojans or worms :P

ConfusedPeopleMover · Answer

That worked fine thanks. 

I would be interested to know why an "address(match..." function wouldn't work for this. I have had some luck with this in the past but this time it didn't work.

rizvisa1 · Answer

It was a multidimensional array (YOUR GRID). I could get match work for a single dimensional array. There might be a way, I was just unable to come  up with match and multidimensional array

Match with conditional format in excel 2003

6 responses

Similar discussions

Subscribe To Our Newsletter!