<table style="width: 427px; height: 126px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:3840;width:79pt" width="105"> <col style="mso-width-source:userset;mso-width-alt:2486; width:51pt" span="3" width="68"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;width:79pt" height="17" width="105">Category
</td> <td class="xl24" style="border-left:none;width:51pt" width="68">Property1</td> <td class="xl24" style="border-left:none;width:51pt" width="68">Property2</td> <td class="xl24" style="border-left:none;width:51pt" width="68">Property3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">name1</td> <td class="xl25" style="border-top:none;border-left:none">NA</td> <td class="xl25" style="border-top:none;border-left:none">NA</td> <td class="xl25" style="border-top:none;border-left:none">NA</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">name2</td> <td class="xl26" style="border-top:none;border-left:none">YES</td> <td class="xl27" style="border-top:none;border-left:none">NO</td> <td class="xl28" style="border-top:none;border-left:none">X</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">name3</td> <td class="xl29" style="border-top:none;border-left:none">YES</td> <td class="xl28" style="border-top:none;border-left:none">X</td> <td class="xl27" style="border-top:none;border-left:none">NO</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">name4</td> <td class="xl29" style="border-top:none;border-left:none">YES</td> <td class="xl28" style="border-top:none;border-left:none">X</td> <td class="xl28" style="border-top:none;border-left:none">X</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">name5</td> <td class="xl27" style="border-top:none;border-left:none">NO</td> <td class="xl27" style="border-top:none;border-left:none">NO</td> <td class="xl27" style="border-top:none;border-left:none">NO</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">name6</td> <td class="xl29" style="border-top:none;border-left:none">
</td> <td class="xl28" style="border-top:none;border-left:none">
</td> <td class="xl25" style="border-top:none;border-left:none">
</td> </tr> </tbody></table>
(fills should be colored instead of fonts)
What I need is a VBA code for making this:
There are various categories listed in Column A.
User should select the value of Property2 (Column C) and Property3 (Column D) from a dropdown incell list (Values can be NA, NO, 1, 2, 3, A, B, C)
(X refers to either one of these: 1, 2, 3, A, B, C)
the colors of the cells for Column C and Column D shall change depending on the selected value (gray for NA, pink for NO and green for X)
The content and the color of Property1 (Column B) shall not be changed by user, it should be automatically changed depending on the value of Property2 (Column C) and Property3 (Column D)
possible combinations are listed below:
- If Property2 is X, then Property1 is YES and green
- If Property2 is NO and Property3 is X, then Property1 is YES and yellow
- If Property2 is NO and Property3 is NO, then Property1 is NO and pink
- If Property2 is NA and Property3 is NA, then Property1 is NA and gray
- If Property2 and Property3 is empty, then Property1 is empty and white
</td> <td class="xl24" style="border-left:none;width:51pt" width="68">Property1</td> <td class="xl24" style="border-left:none;width:51pt" width="68">Property2</td> <td class="xl24" style="border-left:none;width:51pt" width="68">Property3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">name1</td> <td class="xl25" style="border-top:none;border-left:none">NA</td> <td class="xl25" style="border-top:none;border-left:none">NA</td> <td class="xl25" style="border-top:none;border-left:none">NA</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">name2</td> <td class="xl26" style="border-top:none;border-left:none">YES</td> <td class="xl27" style="border-top:none;border-left:none">NO</td> <td class="xl28" style="border-top:none;border-left:none">X</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">name3</td> <td class="xl29" style="border-top:none;border-left:none">YES</td> <td class="xl28" style="border-top:none;border-left:none">X</td> <td class="xl27" style="border-top:none;border-left:none">NO</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">name4</td> <td class="xl29" style="border-top:none;border-left:none">YES</td> <td class="xl28" style="border-top:none;border-left:none">X</td> <td class="xl28" style="border-top:none;border-left:none">X</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">name5</td> <td class="xl27" style="border-top:none;border-left:none">NO</td> <td class="xl27" style="border-top:none;border-left:none">NO</td> <td class="xl27" style="border-top:none;border-left:none">NO</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">name6</td> <td class="xl29" style="border-top:none;border-left:none">
</td> <td class="xl28" style="border-top:none;border-left:none">
</td> <td class="xl25" style="border-top:none;border-left:none">
</td> </tr> </tbody></table>
(fills should be colored instead of fonts)
What I need is a VBA code for making this:
There are various categories listed in Column A.
User should select the value of Property2 (Column C) and Property3 (Column D) from a dropdown incell list (Values can be NA, NO, 1, 2, 3, A, B, C)
(X refers to either one of these: 1, 2, 3, A, B, C)
the colors of the cells for Column C and Column D shall change depending on the selected value (gray for NA, pink for NO and green for X)
The content and the color of Property1 (Column B) shall not be changed by user, it should be automatically changed depending on the value of Property2 (Column C) and Property3 (Column D)
possible combinations are listed below:
- If Property2 is X, then Property1 is YES and green
- If Property2 is NO and Property3 is X, then Property1 is YES and yellow
- If Property2 is NO and Property3 is NO, then Property1 is NO and pink
- If Property2 is NA and Property3 is NA, then Property1 is NA and gray
- If Property2 and Property3 is empty, then Property1 is empty and white
