VBA Code to Match US Region to the State that is entered

[pawcoyote]

Hi Paul, I am trying to add more to this sheet... What I want to do is Color specific Required Columns Yellow on the MDS Equipment Detail sheet if they are missing info if the first Cell B7 has data entered. I want to keep it in line with the code you already helped me with. Using the specific headers for the required cells. Mfg, Model Name, Model, Serial Number, Equipment ID etc.. I will add more as I want to make them required. I also want to Color the cells in the following Columns on the MDS Equipment Detail sheet Pink if they are Duplicates: Serial Number, Equipment ID, IP Address, Host Name, Oracle Config SN and MAC Address.

[Paul_Hossler]

Play around with this version

[pawcoyote]

Will do, I used conditions but would like to see how to do it in vba.. Thanks

[pawcoyote]

I am looking at it and will post if I have questions. Thanks... I will be having a question in a few on how to look up across multiple spreadsheets and then pull in information from those sheets to one sheet... I will have more details once I get it all put down in my head..

Play around with this version

[pawcoyote]

Hi, a couple of things, the color coding doesn't seem to work unless you exit and enter the MDS tab. Then the color coding doesn't work properly all the time. It seems to work some times and not other. I also have Equipment ID's that are showing as dupes but they arn't. It also doesn't seem to fill in the color unless I change something in another cell..

Also if I clean up one of the dupes it keeps one of them still color coded as being a dupe even though it isn't..

Play around with this version

[pawcoyote]

I was able to add in more item for required as well as change to color of the cells... I am having this issue, it seems that I have to exit the Worksheet and go back in for the script to run properly. I cannot just open the book to the that worksheet and start typing and have the color coding work. Also on Dupes when I fix a dupe it doesn't reset to no color for the cell I didn't have to fix for the dupes it still shows as a dupe...

[Paul_Hossler]

It's only the cell(s) that change that drive the condition testing, just like it was only the state that changed that drove the region field populating

The color coding is tied into the WS change event and works on the single row of the cell(s) that changed (client requires C-G values) or on the single column of the cell(s) that changed (no dup on SN, etc.)


1. You used the worksheet_activate event to show your userform so I just included the column number determination in that so it only needs to run once per ws activation

2. The column number of the cell(s) that changed determines the checks that are made (I did forget the Model column in ver 10)

a. If Client (B:B) changes then C:G are checked to see if they're blank in that row

b. If C thru G change, then

if Client is blank then clear color in that row for that cell

if client is not blank, then if the changed cell is blank, make it red else clear color in that row for that cell

3. If a cell in a no dups column is change, then check the column for dups in that column


I could easily check every cell for every condition for every change, but that will add user time by checking cells that were not changed

I could add a sub to check every condition


Either is easy

Do this -- clear the color on all the data rows and make changes (I did not see any unmarked dups)

Make changes and let me know

Capture.JPG

[Paul_Hossler]

This is a little slower and brute force

EVERY change to MDS causes ALL the checks to be reapplied to ALL cells, including the original requirement to translate State to Region

[pawcoyote]

I will check out both, I appreciate all your help.

This is a little slower and brute force

EVERY change to MDS causes ALL the checks to be reapplied to ALL cells, including the original requirement to translate State to Region

[Paul_Hossler]

I'm guessing that you'll prefer ver 12, even though it's a little slower

[pawcoyote]

Hi, I am still not able to see how the cells clear the fill in color once you have fixed the issue. They still stay either Red or Violet. The Duples are checked by the Columns that the data is keyed into. The missing required information is based off the the initial entry of the Client Name and should change to no color as we progress through the sheet. I would like it not to be triggered only when you enter the worksheet for the first time but instantaneously anytime you are on that sheet and working. If there is an easier way to have the legend popup I am game for that as well. I am also looking into how I can have it show again by a short cut key but that is later... I don't want to have to exit and enter the sheet each time to have it do validation checks and or run anything. Does that make sense?

Sample 11 the changing of Colors worked with the exception of the Dupe check it would clear the color of the one you fixed but leave the other one as still showing as a dupe.

Sample 12 none of the colors change..

I'm guessing that you'll prefer ver 12, even though it's a little slower

[Paul_Hossler]

I missed a check

Try ver 13

[pawcoyote]

Testing it out. I did notice the following code

.EntireRow.Cells(colRegion).Interior.Color = vbRed

It is not coloring the Region field when empty.. If that is suppose to do that.. Also How can I change the Color of the Cell to be a ColorIndex and the Font to a different Color and then revert back to the default of no fill and auto font?

I missed a check

Try ver 13

[Paul_Hossler]

A blank Region was not on the original list since it was populated by entering a state (your very first request)

I marked a blank state and region now

You wanted dups marked in pink, do you mean that to want to enter the color index? I put two Const at the top so you can change

Capture.JPG

[pawcoyote]

Hi, No I noticed the code in the VBA area to color it red and was wondering what it meant. I have added in a bunch of other Required columns into the VBA area copying what you started but I am getting errors when it runs. I have uploaded the Sample 15 I like the color indexes better since they allow for more colors to be used.. Not sure what I did wrong on the code...

What I was looking for in colors are as such..

Duplicates ColorIndex = 38 fill and 9 Text on Duplicates found.. Default when fixed or no duplicates

Required Fields Colorindex = 36 fill and Text Default

I had found how to make those changes in your Sample 13 but when I tried to add more Required fields into the code it threw errors..

A blank Region was not on the original list since it was populated by entering a state (your very first request)

I marked a blank state and region now

You wanted dups marked in pink, do you mean that to want to enter the color index? I put two Const at the top so you can change

Capture.JPG

[Paul_Hossler]

You had some typos on the column headers your were matching (I marked them in the macro)

I decided that since there seems like there will be a changes to your column headers add a ColHeaderNumber function that will tell you what the missing column header is

Anyway, here's ver 16 for you to check out

[pawcoyote]

Thank you, The color coding works great, but the Region Filling in or deleting when you enter the state and the Oracle Site Reference filling in or removing does not work...

You had some typos on the column headers your were matching (I marked them in the macro)

I decided that since there seems like there will be a changes to your column headers add a ColHeaderNumber function that will tell you what the missing column header is

Anyway, here's ver 16 for you to check out

[Paul_Hossler]

I added the .Offset line to clear the rest of the line if Client is blank like in Line 7

'   ------------------------------------------- client
    For Each rCell In rData.Columns(colClient).Cells
        With rCell
            If Len(.Value) = 0 Then
                .EntireRow.Interior.Color = xlNone
                .EntireRow.Font.ColorIndex = xlColorIndexAutomatic
                .Offset(0, 1).Resize(1, .Parent.Columns.Count - 2).ClearContents
            End If
        End With
    Next

Clearing and entering a State seems to clear the Region and Oracle fields like in line2

Capture.JPG

Any more details?

[pawcoyote]

Thanks, I found it I fat fingered something once again... I will be looking at everything. I do want to work more on this and add onto it if that is okay... I love what you are helping me with..

I added the .Offset line to clear the rest of the line if Client is blank like in Line 7

'   ------------------------------------------- client
    For Each rCell In rData.Columns(colClient).Cells
        With rCell
            If Len(.Value) = 0 Then
                .EntireRow.Interior.Color = xlNone
                .EntireRow.Font.ColorIndex = xlColorIndexAutomatic
                .Offset(0, 1).Resize(1, .Parent.Columns.Count - 2).ClearContents
            End If
        End With
    Next

Clearing and entering a State seems to clear the Region and Oracle fields like in line2

Capture.JPG

Any more details?

[pawcoyote]

Hi, I noticed if I clear the Client Name it removes everything from every cell in that row. Is there a way to not do that?

I added the .Offset line to clear the rest of the line if Client is blank like in Line 7

'   ------------------------------------------- client
    For Each rCell In rData.Columns(colClient).Cells
        With rCell
            If Len(.Value) = 0 Then
                .EntireRow.Interior.Color = xlNone
                .EntireRow.Font.ColorIndex = xlColorIndexAutomatic
                .Offset(0, 1).Resize(1, .Parent.Columns.Count - 2).ClearContents
            End If
        End With
    Next

Clearing and entering a State seems to clear the Region and Oracle fields like in line2

Capture.JPG

Any more details?