Visual Basic Rule for Excel
Budget: $10 – $30 USD
Hi I have a data file, for which I need to create Visual Basic script to write these rules for example. Sample rules posted below for reference. Final set of rules will be shared with the selected coder.
Rules based on Asset Condition:
1. If AssetCondition=Good/Fair-Food and ReviseRul=0 and RULCalculated<=10, highlight all 3 cells.
2. If AssetCondition=Good/Fair-Good and ReviseRul=1 and RULOverride<=10, highlight all 3 cells.
3. If AssetCondition=Fair/Poor-Fair/Poor and ReviseRul=0 and RULCalculated>10, highlight all 3 cells.
4. If AssetCondition=Fair/Poor-Fair/Poor and ReviseRul=1 and RULOverride>10, highlight all 3 cells.
5. If AssetCondition=Fair/Poor-Fair/Poor and PlanType!=Deferred Maintenance, highlight both cells.
6. If AssetCondition=Good/Fair-Food and PlanType=Deferred Maintenance, highlight both cells.
7. If AssetCondition=Good and ConditionRating!=0, highlight both cells.
8. If AssetCondition=Fair-Good and ConditionRating!=1, highlight both cells.
9. If AssetCondition=Fair and ConditionRating!=2, highlight both cells.
10. If AssetCondition=Poor-Fair and ConditionRating!=3, highlight both cells.
11. If AssetCondition=Poor and ConditionRating!=4, highlight both cells.
Rules based on Priority:
12. If Priority=Priority 1 and Level 5 does not include any of the following words=UPS, Emergency, Fire, Annunciation, Generator, Sprinkler, highlight both cells.
13. If Priority=Priority 2 or Priority 3 and Level 5 includes any of the following words=UPS, Emergency, Fire, Annunciation, Generator, Sprinkler, highlight both cells.
14. If Priority=Priority 2 and PlanType!=Deferred Maintenance, highlight both cells.
15. If Priority=Priority 3 and PlanType=Deferred Maintenance, highlight both cells.
Rules based on PlanType
16. If PlanType=Deferred Maintenance and ReviseRUL=0 and RULCalculated>5, highlight all 3 cells.
17. If PlanType=Deferred Maintenance and ReviseRUL=1 and RULOverride>5, highlight all 3 cells.
Age and RUL based Rules
18. If Age!=Current Year-YearinService, highlight both cells.
19. If RULCalculated!=YearinService+EUL-Current Year, highlight all 3 cells.
20. If RULOverride=blank and ReviseRUL=1, highlight both cells.
21. If ReviseRUL=1 and RULOverride=blank, highlight both cells.
22. If ReviseRUL=0 and RULOverride!blank, highlight both cells.
Rules based on Asset Condition:
1. If AssetCondition=Good/Fair-Food and ReviseRul=0 and RULCalculated<=10, highlight all 3 cells.
2. If AssetCondition=Good/Fair-Good and ReviseRul=1 and RULOverride<=10, highlight all 3 cells.
3. If AssetCondition=Fair/Poor-Fair/Poor and ReviseRul=0 and RULCalculated>10, highlight all 3 cells.
4. If AssetCondition=Fair/Poor-Fair/Poor and ReviseRul=1 and RULOverride>10, highlight all 3 cells.
5. If AssetCondition=Fair/Poor-Fair/Poor and PlanType!=Deferred Maintenance, highlight both cells.
6. If AssetCondition=Good/Fair-Food and PlanType=Deferred Maintenance, highlight both cells.
7. If AssetCondition=Good and ConditionRating!=0, highlight both cells.
8. If AssetCondition=Fair-Good and ConditionRating!=1, highlight both cells.
9. If AssetCondition=Fair and ConditionRating!=2, highlight both cells.
10. If AssetCondition=Poor-Fair and ConditionRating!=3, highlight both cells.
11. If AssetCondition=Poor and ConditionRating!=4, highlight both cells.
Rules based on Priority:
12. If Priority=Priority 1 and Level 5 does not include any of the following words=UPS, Emergency, Fire, Annunciation, Generator, Sprinkler, highlight both cells.
13. If Priority=Priority 2 or Priority 3 and Level 5 includes any of the following words=UPS, Emergency, Fire, Annunciation, Generator, Sprinkler, highlight both cells.
14. If Priority=Priority 2 and PlanType!=Deferred Maintenance, highlight both cells.
15. If Priority=Priority 3 and PlanType=Deferred Maintenance, highlight both cells.
Rules based on PlanType
16. If PlanType=Deferred Maintenance and ReviseRUL=0 and RULCalculated>5, highlight all 3 cells.
17. If PlanType=Deferred Maintenance and ReviseRUL=1 and RULOverride>5, highlight all 3 cells.
Age and RUL based Rules
18. If Age!=Current Year-YearinService, highlight both cells.
19. If RULCalculated!=YearinService+EUL-Current Year, highlight all 3 cells.
20. If RULOverride=blank and ReviseRUL=1, highlight both cells.
21. If ReviseRUL=1 and RULOverride=blank, highlight both cells.
22. If ReviseRUL=0 and RULOverride!blank, highlight both cells.