conditional formatting or countif..?

RAJESH1960

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
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
 
Upvote 0
Solution
Peter_SSs said:
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
Click to expand...
I pasted the formula in "use a formula to determine which cells to format". But I am getting the wrong cells colored
 
Upvote 0
RAJESH1960 said:
I pasted the formula in "use a formula to determine which cells to format". But I am getting the wrong cells colored
Click to expand...
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
 
Upvote 0
Peter_SSs said:
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
Click to expand...
Sorry, Same mistake. Selected the whole column. Had to select only the cells. It's good now. Thanks Peter
 
Upvote 0
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)
 
Upvote 0
Peter_SSs said:
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)
Click to expand...
:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
how to convert scientifical number to general number format
Replies
4
Views
102
Lacampeona
L
M
  • Question
Excel Consecutive Formula
2
Replies
10
Views
309
mahmed1
M
S
  • Solved
Rounding time
2
Replies
18
Views
569
felixstraube
F
C
  • Question
SUMIFS if a date is within the range
Replies
1
Views
166
Fluff
Fluff
R
  • Question
SUMIFS between two dates not working :S
2
Replies
11
Views
572
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,646
Messages
6,120,717
Members
448,985
Latest member
chocbudda

Share this page

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
Back
Top