VBA Code for Highlighting Cells based on If then Rules

[ShwetaD]

Hi, Attached is the excel data file for which I need to create a visual basic code with the following rules.

1. If AssetCondition=Good and ReviseRul=0 and RULCalculated<=10, highlight all 3 cells.
2. If AssetCondition=Good and ReviseRul=1 and RULOverride<=10, highlight all 3 cells.
3. If Priority=Priority 1 and Level 5 does not include any of the following words=UPS, Emergency, Fire, Annunciation, Generator, Sprinkler, highlight both cells.
4. If RULCalculated!=YearinService+EUL-Current Year, highlight all 3 cells.
5. IfUnitCost field is blank, then highlight that cell.
6. If Manufacturer field is blank, then input "Not Visible" in cell
7. If CapacityUnitOfMeasure=numeric value, highlight cell.
8. Update all cells in PlanType column as capital letters.
9. If YearManufactured contains comma then highlight cell.

Sample Data File.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH
1	AssetID	ClientName	Agency	Campus	Building	Level1	Level2	Level3	Level4	Level5	GuidCSICode	UnitCost	UnitofMeasure	DeficiencyClass	REFNumber	AssetCondition	PlanType	Priority	Quantity	YearinService	AssetLocation	MoreSpecificLocation	Capacity	CapacityUnitOfMeasure	Manufacturer	ModelNumber	BarcodeNumber	AssetTag	SerialNumber	AssetLabel	Age	IsIncludeReplaceInPlanItem	AssessmentYear	InterventionStatus	Warranty	YearManufactured	Description	Condition	ConditionRating	Notes	HasImages	ReviseRUL	RULCalculated	RULOverride	UnadjustedTotalCost	EUL	AnnualSavings	SimplePaybackYears	UnitCostSavings	ParentAssetID	ClientID	AgencyID	CampusID	BuildingID	ClientGUID	AgencyGUID	CampusGUID	BuildingGUID	IsActionItem	DateAssessed
2	1	Lau	N/A	0079b	Bg1	A SubStructure	A10 Foundations	A1000 Foundations	A1000 Foundations	A100001 Engineering Study of Foundation		5000	EACH	Scheduled Action			Plan Type 1 - Deferred Maintenance	Priority 2 - Currently Critical	1	2023										Engineering Study of Foundation		1									0	0			5000	60	0			UOL-ULB-DURBLDG-0079JM-B201128-4									1
3	2	Lau	N/A	0079b	Bg3	B Shell	B20 Exterior Enclosure	B2030 Exterior Doors	B2032 Solid Exterior Doors	B203203 Paint Exterior Doors		2.7405	SF	Scheduled Action			Plan Type 1 - Deferred Maintenance	Priority 3 - Necessary / Not Critical	5	2023										Refinish Glazed Exterior Doors		1									0	0			1644.3	30	0			UOL-ULB-NATURALSCIENCE-0034PH-B203100-7									1
4	3	Lau	N/A	0079b	Bg1	B Shell	B20 Exterior Enclosure	B2030 Exterior Doors	B2032 Solid Exterior Doors	B203203 Paint Exterior Doors		2.7405	SF	Scheduled Action			Plan Type 2 - Routine Maintenance	Priority 3 - Necessary / Not Critical	1	2023										Paint Exterior Doors		1									0	0			230.202	30	0			UOL-ULB-NATURALSCIENCE-0034PH-C102103-9									1
5	4	Lau	N/A	0079b	Bg1	B Shell	B20 Exterior Enclosure	B2030 Exterior Doors	B2032 Solid Exterior Doors	B203203 Paint Exterior Doors		2.7405	SF	Scheduled Action			Plan Type 2 - Routine Maintenance	Priority 3 - Necessary / Not Critical	20	2023										Refinish Interior Doors		1									0	0			24861.816	30	0			UOL-ULB-NATURALSCIENCE-0034PH-C102105-14									1
6	5	Lau	N/A	0079b	Bg1	B Shell	B20 Exterior Enclosure	B2030 Exterior Doors	B2039 Other Doors and Entrances	B203909 Paint Exterior Doors		2.7405	SF	Scheduled Action			Plan Type 2 - Routine Maintenance	Priority 3 - Necessary / Not Critical	5	2023										Paint Exterior Doors		1									0	0			575.505	50	0			UOL-ULB-NATURALSCIENCE-0034PH-C102109-8									1
Sheet1

Can someone please help me write this rule. Thanks in advance

 

[Dan_W]

Can I ask, why aren't you using conditional formatting for this?

 

[ShwetaD]

2 reasons
1. The above rules posted are only a sample, the overall set of rules about 50 rules and not all include highlighting eg. please see item 6 and 8.
2. Conditional formatting with soo many rules and 8000 rows data would make the file slow.

But I could be wrong and more than happy for you to advise how this can be achieved using conditional formatting. Please let me know.

 

[Dan_W]

1. 
   Today, 11:20 PM#3
   SKD7
   
   
   
   VBAX Newbie
   
   JoinedNov 2022Posts2Location
   
   

   
   

   

Not sure if you intended to post this ...

 

[ShwetaD]

This is what I intended to reply

2 reasons
1. The above rules posted are only a sample, the overall set of rules about 50 rules and not all include highlighting eg. please see item 6 and 8.
2. Conditional formatting with soo many rules and 8000 rows data would make the file slow.

But I could be wrong and more than happy for you to advise how this can be achieved using conditional formatting. Please let me know.

 

[Dan_W]

Hmm. I see that you posted here yesterday on a different but related (?) topic. I see also that conditional formatting was suggested there too. Let me read it and see why they didn't follow through with that.

 

[Dan_W]

So is this a duplicate thread?

 

[Dan_W]

You've asked the same question at a different forum.

VBA Code for Highlighting Cells based on If then Rules

Hi, Attached is the excel data file 30331 for which I need to create a visual basic code with the following rules. If AssetCondition=Good and ReviseRul=0 and RULCalculated<=10, highlight all 3 cells. If AssetCondition=Good and ReviseRul=1 and RULOverride<=10, highlight...

www.vbaexpress.com

 

[Fluff]

Duplicate to: VBA Code if this and that, then highlight both cells.

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.