Color Cell Based On Different Cell Value

    Hi,
    I've been trying to shade a cell (A2) a certain color based on the value in another cell (B2), but I can't seem to get there with the code I've pieced together from other code snippets.


    I have a table of cities ("contacts200805_table") where the city cells are color coded based on which person is assigned to work in that city. People are often reassigned to different cities, so I'm trying to create a macro that will update the cell color for me.


    I use a different software to generate the list of people and their assigned cities. I copy the list into a table ("input_contacts"). The city info populates A2:A1043. The person's name populates B2:B1043. "Input_contacts" and "contacts200805" have the exact same cities and always will. The people will change. I use the "input contacts" table to just dump the new list. The "contacts200805" table is what I use for the report, so the format can't be changed.


    Here is what I want my macro to do:
    start macro
    select the "input contacts" table
    copy the people in range B2:B118
    select the "contacts200805" table
    paste the people in B2:B118 which is next to the city column (A2:A118)
    repeat for other columns


    look at the name in cell B2 on the "contacts200805" table
    if name = jane doe then color the city cell (A2) red
    if name= john hanson then color city cell green, etc. (I have 6 people to compare)
    repeat check for all cells that contain a persons name (columns B, D, F, H, J, L, N, P & R)


    make the person's name text color white so it doesn't show up on the final printout
    end macro


    When I run the macro below I get a 'type mismatch' error at the first case select



    I've also attached sample xls file. The colors and names may not match up correctly because I changed the names for the sample.


    Any suggestions is greatly appreaciated,
    Charlotte

    Re: Color Cell Based On Different Cell Value


    Hi,
    I can't use conditional formatting because I have more than 3 conditions. I've tried to research the answer to my problem, but I haven't been able to get any codes to work for my situation. The codes I've seen apply to a single column not multiple, discontiguous like mine.
    Thanks,
    Charlotte

    Re: Color Cell Based On Different Cell Value


    You really need to use absolute referencing and not use Select. e.g.

    Code
    Sheets("input_contacts").Range("B2:B118").Copy Sheets("contacts200805_table").Range("B2")


    You may even need a more robust method to pick the range B2 to the last row with data. Post another question if you need help with that or look through the forum. There are many examples that do it.


    This should fix your Select Case problem.


    Of course you would probable be better off doing this as a Sheet Change event unless this is a one time deal or do the copy paste for one part and let the Change event handle the color markups.

    Re: Color Cell Based On Different Cell Value


    Thanks Kenneth,
    I was able to make the code more efficient AND work with your suggestions.
    Charlotte

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login