Special conditional formatting; highlight the series

Kishan · Jul 31, 2018

Using Excel 2000

Hi,

I am looking special conditional formatting; perhaps it would not be possible doing by the formula. Need your help with a VBA solution 

My series is made of three characters 1, X and 2

I want to highlight every series in 2 different colours as shown in the given example below

Here is a example of row 6, and will be followed the same method for all the rest rows bellows, Series start with "1" and will end with "2"

C6 = X its alone series end, because D6 has "1" so highlight C6 with Green
D6 = 1 and E6 = 2 series end, because F6 has "1" so highlight D6:E6 with Red
F6:G6 Series with colour Green
H6:I6 series with colour Red
J6:L6 series with colour Green
And last M6:P6 with colour Red 

Result data example

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

2

3

4

5

P1

P2

P3

P4

P5

P6

P7

P8

P9

P10

P11

P12

P13

P14

6

X

1

2

1

2

X

1

2

1

X

2

7

1

X

2

1

X

2

X

1

8

1

X

1

X

2

X

1

X

9

1

X

1

2

1

X

1

X

1

2

1

10

X

1

X

1

X

1

X

1

X

1

11

X

1

X

1

X

2

1

2

X

12

2

X

1

2

X

1

2

1

13

X

1

X

1

X

1

14

2

1

X

2

1

X

2

X

2

X

15

X

1

2

X

1

2

1

X

16

1

2

1

2

X

2

1

X

1

2

X

1

17

2

X

1

2

1

2

1

X

18

1

2

1

2

1

2

1

X

19

1

2

1

2

1

2

1

2

1

X

20

21

Sheet2

Thank you in advance

Regards,
Kishan

Kishan · Jul 31, 2018

Re: Need help with a special conditional formatting; highlight the series

Hi, if any formula can tweak this will be the perfect. Please suggest some

Thank you

Regards,
Kishan

Kishan · Jul 31, 2018

Re: Need help with a special conditional formatting; highlight the series

Hi, is it possible if I make a table in columns R:Z with numbers to be highlighted columns C:P accordingly numbers of R:Z like the example below

Book1

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

1

2

3

4

5

P1

P2

P3

P4

P5

P6

P7

P8

P9

P10

P11

P12

P13

P14

Color1

Color2

Color3

Color4

Color5

Color6

Color7

Color8

Color9

6

X

1

2

1

2

X

1

2

1

X

2

1

2

3

4

7

1

X

2

1

X

2

X

1

5

2

8

1

X

1

X

2

X

1

X

2

6

2

4

9

1

X

1

2

1

X

1

X

1

2

1

2

3

2

6

1

10

X

1

X

1

X

1

X

1

X

1

2

1

3

5

1

11

X

1

X

1

X

2

1

2

X

3

5

3

2

1

12

2

X

1

2

X

1

2

1

4

3

4

1

13

X

1

X

1

X

1

3

8

2

14

2

1

X

2

1

X

2

X

2

X

1

4

1

15

X

1

2

X

1

2

1

X

1

3

1

2

7

16

1

2

1

2

X

2

1

X

1

2

X

1

2

3

4

17

2

X

1

2

1

2

1

X

5

4

2

3

18

1

2

1

2

1

2

1

X

5

4

2

3

19

1

2

1

2

1

2

1

2

1

X

2

5

2

3

20

21

Sheet2

Thank you

Regards,
Kishan

Kishan · Aug 1, 2018

Re: Need help with a special conditional formatting; highlight the series

Bump

Kishan · Aug 1, 2018

Re: Need help with a special conditional formatting; highlight the series

Hi, actually in post#3 I noticed that I had wrong numbers row R16:Z17 which to be filled colours in the column C:P. here is the corrected example table

Book1

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

1

2

3

4

5

P1

P2

P3

P4

P5

P6

P7

P8

P9

P10

P11

P12

P13

P14

Color1

Color2

Color3

Color4

Color5

Color6

Color7

Color8

Color9

6

X

1

2

1

2

X

1

2

1

X

2

1

2

3

4

7

1

X

2

1

X

2

X

1

5

2

8

1

X

1

X

2

X

1

X

2

6

2

4

9

1

X

1

2

1

X

1

X

1

2

1

2

3

2

6

1

10

X

1

X

1

X

1

X

1

X

1

2

1

3

5

1

11

X

1

X

1

X

2

1

2

X

3

5

3

2

1

12

2

X

1

2

X

1

2

1

4

3

4

1

13

X

1

X

1

X

1

3

8

2

14

2

1

X

2

1

X

2

X

2

X

1

4

1

15

X

1

2

X

1

2

1

X

1

3

1

2

7

16

1

2

1

2

X

2

1

X

1

2

X

1

2

1

3

17

2

X

1

2

1

2

1

X

1

2

3

4

18

1

2

1

2

1

2

1

X

5

4

2

3

19

1

2

1

2

1

2

1

2

1

X

2

5

2

3

20

21

Sheet2

Thank you

Regards,
Kishan

nikio8 · Aug 1, 2018

Re: Need help with a special conditional formatting; highlight the series

Hi not sure about your logic,
you can use

Range A
For each cell in A
if cell.value =1 then cell.interior. setcolour=2
else if cell.value =2 then cell.interior. setcolour=3

some condition
reset colour number = 1
otherwise keep using colour number 2

Kishan · Aug 1, 2018

Re: Need help with a special conditional formatting; highlight the series

nikio8 said:
Hi not sure about your logic,
you can use

Range A
For each cell in A
if cell.value =1 then cell.interior. setcolour=2
else if cell.value =2 then cell.interior. setcolour=3

some condition
reset colour number = 1
otherwise keep using colour number 2

Hi nikio8, thank you for your response, here is the example for row 6 and will be applied same rules for all rows.

Book1

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

1

2

3

4

5

P1

P2

P3

P4

P5

P6

P7

P8

P9

P10

P11

P12

P13

P14

Color1

Color2

Color3

Color4

Color5

Color6

Color7

Color8

Color9

6

X

1

2

1

2

X

1

2

1

X

2

1

2

3

4

Sheet2

Really I am looking 2 colours conditional formatting. 

CF formula needs to check the series for "1", "X" and "2"; where "1" is a start of character of the series and "2" is the end of series.

If you observe in the C6 = "X" neither "1" before nor "2" is after so "X" stand alone in the series for "X2" colour cell C6 with green.

Search for next "1X2" series D6 = "1" and E6 = "2" series over Colour D6:E6 in Red.

Search for next "1X2" series F6 = "1" and G6 = "2" series over Colour F6:G6 in Green.

Search for next "1X2" series H6 = "X" and I6 = "X" series over Colour H6:I6 in Red.

Search for next "1X2" series J6 & K6 = "1" and L6 = "2" series over Colour J6, K6, L6 in Green.

Search for next "1X2" series M = "1" N6 = "X" and O6 & P6 = "2" series over Colour M6, N6, O6, P6 in Red.

Resume: keep changing the colours when the "1X2" series is completed. 

This is what I wanted as per post#1.

But later I realized might it not work like this so far in the post#5 I added the extra columns R:Z numbers of columns to be coloured to make bit easier, if it could be done directly without R:Z columns will be the fantastic

Thank you

Regards,
Kishan

Kishan · Aug 1, 2018

Re: Need help with a special conditional formatting; highlight the series

Bump

Kishan · Aug 1, 2018

Re: Need help with a special conditional formatting; highlight the series

Bump

Kishan · Aug 2, 2018

Re: Need help with a special conditional formatting; highlight the series

Hi, 205 views I could imagine this colouring has a high difficulty level. May some one can suggest other way, I mean using some helper columns can be achieved to do this will be also perfect I have no problem to adapt any layout.

Thank you

Regards,
Kishan

Special conditional formatting; highlight the series

Kishan

Well-known Member

Excel Facts

Kishan

Well-known Member

Kishan

Well-known Member

Kishan

Well-known Member

Kishan

Well-known Member

nikio8

Board Regular

Kishan

Well-known Member

Kishan

Well-known Member

Kishan

Well-known Member

Kishan

Well-known Member

Similar threads

Forum statistics

Share this page

Special conditional formatting; highlight the series

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Board Regular

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock