VBA for color coding a cell based on values in other cells

[kalyanr12]

Experts,

I have attached a sample Excel sheet. I need some help in writing macro to set color code the column E (status column) based on the values of column G for a group (column A).

a) Basically in the attached example, for ABS group starting at A10, all I need is to get all the cell values from G10 until there is a change in group in column (in the example ACM), so I would get G10, G11 and G12. If all of them are empty, then color code the E10 cell to RED. In the attached example, I would get only value of Vehicle Configuration Complete so I need to set the color as GREEN on E10.

b) Once the group changed to ACM, then for that group get all the cell values in column G, from that row until there is a change in group (e.x FCIM). If all of them are empty, then color code the cell E13 to RED. In the attached example, there are 2 values Definition Approved and Draft. If we find at least one of the values as "Draft", then set the cell E13 color to RED.

c) In the same way for every group , we need to get the column G cell values till there is a group change. From the retrieved values, if all of them say Vehicle Configuration complete, then set the status of column E row (matching that of group) to GREEN. If at least one of them is Draft, then color code to RED. If all are Definition Approved, then color code to YELLOW.

These are 3 rules that I want to apply and set the color code accordingly.

Appreciate if someone can help me with the macro for the same.

[Sintek]

Not to sure about this one...perhaps ????

Please Login or Register  to view this content.

[kalyanr12]

Hello Sinket,

Appreciate a lot. It works like a charm. I just had to make some minor changes to suit my real data and as mentioned works superb.

Thanks,
Kalyan

[Sintek]

Glad I could help. Please mark thread as solved, thanks for rep added...

[kalyanr12]

Hello Sinket,

I was trying out the macro on different test cases and it seems that the color coding logic is not working for some. The excel sheet that I attached has some issues with the color coding.

First of all for a value in column A say PAM and PDM, there are no corresponding status values and I was expecting them to show as RED, but they are not getting processed in the for loop. They are actually getting skipped and so no color is getting set. Also, here are my rules to set the color code:

1) If there are no child rows for a group, color should be set to red (blank)
2) If group has one or more child rows then here's the logic that I am looking at:

a) If there is only status row with Definition Approved or Waiting for Netcom Approval or Pending Approval then color code should be yellow
b) If there is only one status row with Frozen or Vehicle Configuration Complete, then color code is green
c) If there is only one status row with Draft or Incomplete or Rejected, then color code is red
d) If there are more than one rows then containing one Draft or Incomplete or Rejected should set to red
e) If there are more than one rows with Frozen, Vehicle Configuration Complete and (Definition Approved or Waiting for Netcom Approval or Pending Approval), then color is yellow
f) If there are more than one rows with Frozen or Vehicle Configuration Complete combination only, then color is Green

With the logic you provided, the color coding is not getting set as mentioned above.

My other request is I need to group all rows belonging to a value in column A. Basically for ABS group, ABS0, ABS1, ABS1 are child rows, so I want main row to show up with the "+" sign by default. If needed users can click this plus button and expand to the child rows. I want this grouping to be for every group.

Appreciate if you can help me with this as my customer is really after me.