kalyanr12
09-11-2017, 07:58 PM
Experts,
I have attached a sample Excel sheet. I need some help in writing macro to set color coding the column H (VSCS pre-TT Signoff column) based on the values of column I for a group (column A).
a) Basically in the attached examples (2 excel files, one is the file containing raw data and in the other one I specified how I want the color coding to be done), for ABS group starting at A7, all I need is to get all the cell values from I8 until there is a change in group in column (in the example ABS), so I would get I8, I9 and I10 and accordingly set the cell value H7 with proper color. I have mentioned the rules below for color coding.
b) For groups like AAM, ACM, DCDC, FCDIM, PAM, PDM, TRCM, VDM there are no child rows having Release status values so for those it is straight forward and we should set the color as RED in the cells H6, H11, H16, H21, H26, H27, H32 and H33 respectively.
Rules for setting color coding:
a) If the child rows for a group have at least one of the release statuses as Draft or INCOMPLETE or Rejected, then the color code in the column H for that group should be RED
b) If there is only child row with release status as Definition Approved or Waiting for Netcom Approval or Pending Approval then color code should be yellow
c) If there is only one status row with Frozen or Vehicle Configuration Complete, then color code is green
d) If there is only one status row with Draft or Incomplete or Rejected, then color code is 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.
g) If there are no child rows, then just set the color as RED in the column H
Lastly, 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 anyone can help me with the VBA script for the same.
I have attached a sample Excel sheet. I need some help in writing macro to set color coding the column H (VSCS pre-TT Signoff column) based on the values of column I for a group (column A).
a) Basically in the attached examples (2 excel files, one is the file containing raw data and in the other one I specified how I want the color coding to be done), for ABS group starting at A7, all I need is to get all the cell values from I8 until there is a change in group in column (in the example ABS), so I would get I8, I9 and I10 and accordingly set the cell value H7 with proper color. I have mentioned the rules below for color coding.
b) For groups like AAM, ACM, DCDC, FCDIM, PAM, PDM, TRCM, VDM there are no child rows having Release status values so for those it is straight forward and we should set the color as RED in the cells H6, H11, H16, H21, H26, H27, H32 and H33 respectively.
Rules for setting color coding:
a) If the child rows for a group have at least one of the release statuses as Draft or INCOMPLETE or Rejected, then the color code in the column H for that group should be RED
b) If there is only child row with release status as Definition Approved or Waiting for Netcom Approval or Pending Approval then color code should be yellow
c) If there is only one status row with Frozen or Vehicle Configuration Complete, then color code is green
d) If there is only one status row with Draft or Incomplete or Rejected, then color code is 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.
g) If there are no child rows, then just set the color as RED in the column H
Lastly, 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 anyone can help me with the VBA script for the same.