Conditional Formatting - With formula

ExcelNewbie2020

Active Member
Joined
Dec 3, 2020
Messages
282
Office Version
  1. 365
Platform
  1. Windows
Experts, is there any way by conditional formatting or by VBA that will solved these.

To highlight cells (3rows) if the value is in numerical sequence (group of 3) but the numbers can be in any order. For example, cells will highlight if the sequence from 1, 2, & 3 will be arranged in any order like 123/132/213/312/231/123/321 please see below. Many thanks

1607519341845.png
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
I'm thinking
=MAX($A1:$C1)-MIN($A1:$C1)=2
as a conditional format formula
 
Upvote 0
Sorry, had not thought that through enough - i'll have a think again
 
Upvote 0
IS this any closer
=AND(MAX(A2:C2)-MIN(A2:C2)=2,(COUNTIF(A2:C2,A2)+COUNTIF(A2:C2,C2))<=2)
Book1
ABCDEFGHI
1NUMBERS MAX/MINCOUNT A2COUNT A3COUNTFORMULA Solution
2123TRUE11TRUETRUE
3231TRUE11TRUETRUE
4456TRUE11TRUETRUE
5645TRUE11TRUETRUE
6402403405FALSE11TRUEFALSE
7324.8324.3326.4FALSE11TRUEFALSE
8405.4404.6407.2FALSE11TRUEFALSE
9486484.9488FALSE11TRUEFALSE
10566.6565.2568.8FALSE11TRUEFALSE
11647.2645.5649.6FALSE11TRUEFALSE
12402403401TRUE11TRUETRUE
13355TRUE12FALSEFALSE
14535TRUE22FALSEFALSE
15535TRUE22FALSEFALSE
16553TRUE21FALSEFALSE
17555FALSE33FALSEFALSE
18FALSE00TRUEFALSE
Sheet1
Cell Formulas
RangeFormula
E2:E18E2=MAX(A2:C2)-MIN(A2:C2)=2
F2:F18F2=COUNTIF(A2:C2,A2)
G2:G18G2=COUNTIF(A2:C2,C2)
H2:H18H2=(COUNTIF(A2:C2,A2)+COUNTIF(A2:C2,C2))<=2
I2:I18I2=AND(MAX(A2:C2)-MIN(A2:C2)=2,(COUNTIF(A2:C2,A2)+COUNTIF(A2:C2,C2))<=2)
 
Upvote 0
Solution
IS this any closer
=AND(MAX(A2:C2)-MIN(A2:C2)=2,(COUNTIF(A2:C2,A2)+COUNTIF(A2:C2,C2))<=2)
Book1
ABCDEFGHI
1NUMBERS MAX/MINCOUNT A2COUNT A3COUNTFORMULA Solution
2123TRUE11TRUETRUE
3231TRUE11TRUETRUE
4456TRUE11TRUETRUE
5645TRUE11TRUETRUE
6402403405FALSE11TRUEFALSE
7324.8324.3326.4FALSE11TRUEFALSE
8405.4404.6407.2FALSE11TRUEFALSE
9486484.9488FALSE11TRUEFALSE
10566.6565.2568.8FALSE11TRUEFALSE
11647.2645.5649.6FALSE11TRUEFALSE
12402403401TRUE11TRUETRUE
13355TRUE12FALSEFALSE
14535TRUE22FALSEFALSE
15535TRUE22FALSEFALSE
16553TRUE21FALSEFALSE
17555FALSE33FALSEFALSE
18FALSE00TRUEFALSE
Sheet1
Cell Formulas
RangeFormula
E2:E18E2=MAX(A2:C2)-MIN(A2:C2)=2
F2:F18F2=COUNTIF(A2:C2,A2)
G2:G18G2=COUNTIF(A2:C2,C2)
H2:H18H2=(COUNTIF(A2:C2,A2)+COUNTIF(A2:C2,C2))<=2
I2:I18I2=AND(MAX(A2:C2)-MIN(A2:C2)=2,(COUNTIF(A2:C2,A2)+COUNTIF(A2:C2,C2))<=2)

Sir, thank you so much for giving time to solve my query.. the formula is ok but when I applied it to conditional formatting it become like this below. Do you have any other solution?..really appreciate your help..

1607580479907.png
 
Upvote 0
Sir, thank you so much for giving time to solve my query.. the formula is ok but when I applied it to conditional formatting it become like this below. Do you have any other solution?..really appreciate your help..

View attachment 27648

Sorry, my bad.. i forgot to remove the previous conditional formatting that I had set... I think, its working perfectly now.. Thank you very much...
 
Upvote 0
ok,
you would need to add $ to fix the test, when the 3 columns are selected, otherwise you would get strange results like that

=AND(MAX($A2:$C2)-MIN($A2:$C2)=2,(COUNTIF($A2:$C2,$A2)+COUNTIF($A2:$C2,$C2))<=2)
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

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