conditional formatting or countif..?

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
474
Office Version
  1. 2019
Platform
  1. Windows
Hello
I have this data. I am trying but not able to apply conditional formatting to the cells of the same number. I have manually colored the cells to show the result I am expecting.
Query to color cells with same number.xlsx
ABCDEFGH
1ABCDEFG
201-01-2020January1543FALSE
301-01-2020February1544FALSE
401-01-2020March1552FALSE
501-01-2020April1552TRUE
601-01-2020May1553FALSE
701-01-2020August2514FALSE
801-01-2020September2515FALSE
901-01-2020October2515TRUE
1001-01-2020November2515TRUE
1101-01-2020December2516FALSE
1201-01-2020May2567FALSE
1301-01-2020June2567TRUE
1401-01-2020July2567TRUE
1501-01-2020August2567TRUE
1601-01-2020September2567TRUE
1701-01-2020October2567TRUE
1801-01-2020November2567TRUE
Query-color cells with same no.
Cell Formulas
RangeFormula
H2:H18H2=E1=E2
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,511
Office Version
  1. 365
Platform
  1. Windows
Like this?

21 03 14.xlsm
ABCDE
1ABCDE
201-01-2020January1543
301-01-2020February1544
401-01-2020March1552
501-01-2020April1552
601-01-2020May1553
701-01-2020August2514
801-01-2020September2515
901-01-2020October2515
1001-01-2020November2515
1101-01-2020December2516
1201-01-2020May2567
1301-01-2020June2567
1401-01-2020July2567
1501-01-2020August2567
1601-01-2020September2567
1701-01-2020October2567
1801-01-2020November2567
CF Matches
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:E18Expression=COUNTIF($E$2:$E$18,$E2)>1textNO
 
Solution

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
474
Office Version
  1. 2019
Platform
  1. Windows
Like this?

21 03 14.xlsm
ABCDE
1ABCDE
201-01-2020January1543
301-01-2020February1544
401-01-2020March1552
501-01-2020April1552
601-01-2020May1553
701-01-2020August2514
801-01-2020September2515
901-01-2020October2515
1001-01-2020November2515
1101-01-2020December2516
1201-01-2020May2567
1301-01-2020June2567
1401-01-2020July2567
1501-01-2020August2567
1601-01-2020September2567
1701-01-2020October2567
1801-01-2020November2567
CF Matches
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:E18Expression=COUNTIF($E$2:$E$18,$E2)>1textNO
I pasted the formula in "use a formula to determine which cells to format". But I am getting the wrong cells colored
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
474
Office Version
  1. 2019
Platform
  1. Windows
I pasted the formula in "use a formula to determine which cells to format". But I am getting the wrong cells colored
Query to color cells with same number.xlsx
ABCDEFGHIJ
1ABCDEFG
201-01-2020January1543=COUNTIF($E$2:$E$18,$E2)>1
301-01-2020February1544
401-01-2020March1552
501-01-2020April1552
601-01-2020May1553
701-01-2020August2514
801-01-2020September2514
901-01-2020October2515
1001-01-2020November2515
1101-01-2020December2516
1201-01-2020May2567
1301-01-2020June2567
1401-01-2020July2567
1501-01-2020August2567
1601-01-2020September2567
1701-01-2020October2567
1801-01-2020November2567
Query-color cells with same no.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E:EExpression=COUNTIF($E$2:$E$18,$E2)>1textNO
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
474
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Like this?

21 03 14.xlsm
ABCDE
1ABCDE
201-01-2020January1543
301-01-2020February1544
401-01-2020March1552
501-01-2020April1552
601-01-2020May1553
701-01-2020August2514
801-01-2020September2515
901-01-2020October2515
1001-01-2020November2515
1101-01-2020December2516
1201-01-2020May2567
1301-01-2020June2567
1401-01-2020July2567
1501-01-2020August2567
1601-01-2020September2567
1701-01-2020October2567
1801-01-2020November2567
CF Matches
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:E18Expression=COUNTIF($E$2:$E$18,$E2)>1textNO
Sorry, Same mistake. Selected the whole column. Had to select only the cells. It's good now. Thanks Peter
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,511
Office Version
  1. 365
Platform
  1. Windows
You appear to have just applied that to column E. In post #1 you showed colour in four columns - B:E.
Which is it that you want, one column or 4?

Edit: I hadn't seen post #5 when I prepared this so it looks like you have it sorted now. (y)
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
474
Office Version
  1. 2019
Platform
  1. Windows
You appear to have just applied that to column E. In post #1 you showed colour in four columns - B:E.
Which is it that you want, one column or 4?

Edit: I hadn't seen post #5 when I prepared this so it looks like you have it sorted now. (y)
:)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,687
Messages
5,637,832
Members
416,984
Latest member
dee10

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