Need help about excel formula (Conditional Formatting)

ybr_15

Board Regular
Joined
May 24, 2016
Messages
193
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Hi, I have data like in the following table and want to mark it with Conditional Formatting (yellow highlight). The criteria used in each Category column (C, D ,,, to H) are:
  1. If the value is 0 or blank then it is marked
  2. If there are the same / duplicate values then marked except the first value
Please help, thank you
Book1
BCDEFGH
2YTDCateg. 1Categ. 2Categ. 3Categ. 4Categ. 5Categ. 6
3Feb-203,935,554163,60312,845-113,32044,553
4Mar-205,542,576228,32112,845-115,840207,657
5Apr-206,132,596270,84212,845336,824145,190207,657
6Mei-207,795,626321,50712,8451,391,168155,290207,657
7Jun-209,520,231413,93212,8452,802,199156,810328,643
8Jul-2011,010,925424,44712,8453,207,172156,810603,340
9Agu-2012,104,287424,44712,8454,399,212156,810901,783
10Sep-2012,734,468424,44712,8454,467,020156,8101,184,573
11Okt-2012,955,248431,31012,8454,467,020157,9941,184,573
12Nov-2013,081,636431,31012,8454,467,020194,3991,310,284
13Des-2013,081,636431,31012,8454,467,020194,3991,310,284
Sheet1
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,483
Office Version
  1. 365
Platform
  1. Windows
Hi, you can try this formula with the "use a formula to determine" method of CF.

Excel Formula:
=OR(B2=B1,B2=0)

Book5
ABCDEFG
1YTDCateg. 1Categ. 2Categ. 3Categ. 4Categ. 5Categ. 6
243862393555416360312845011332044553
3438915542576228321128450115840207657
443922613259627084212845336824145190207657
5439527795626321507128451391168155290207657
6439839520231413932128452802199156810328643
74401311010925424447128453207172156810603340
84404412104287424447128454399212156810901783
944075127344684244471284544670201568101184573
1044105129552484313101284544670201579941184573
1144136130816364313101284544670201943991310284
1244166130816364313101284544670201943991310284
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:G12Expression=OR(B2=B1,B2=0)textNO
 
Solution

ybr_15

Board Regular
Joined
May 24, 2016
Messages
193
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Hi, you can try this formula with the "use a formula to determine" method of CF.

Excel Formula:
=OR(B2=B1,B2=0)

Book5
ABCDEFG
1YTDCateg. 1Categ. 2Categ. 3Categ. 4Categ. 5Categ. 6
243862393555416360312845011332044553
3438915542576228321128450115840207657
443922613259627084212845336824145190207657
5439527795626321507128451391168155290207657
6439839520231413932128452802199156810328643
74401311010925424447128453207172156810603340
84404412104287424447128454399212156810901783
944075127344684244471284544670201568101184573
1044105129552484313101284544670201579941184573
1144136130816364313101284544670201943991310284
1244166130816364313101284544670201943991310284
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:G12Expression=OR(B2=B1,B2=0)textNO
Hi, FormR
OMG, easy as that. I thought my head was blanko_O. Thank you
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top