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 · Aug 2, 2018

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

Any luck with this query

Kishan · Aug 2, 2018

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

Bump

StephenCrump · Aug 3, 2018

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

If you change your helper cells to cumulative totals, you could do it like this:

Conditionally formatted green using formula:
=AND(COLUMNS($A1:A1)<=MAX($P1:$X1),ISODD(MATCH(COLUMNS($A1:A1)-1,$P1:$X1,1)))

Conditionally formatted red using formula:
=AND(COLUMNS($A1:A1)<=MAX($P1:$X1),ISEVEN(MATCH(COLUMNS($A1:A1)-1,$P1:$X1,1)))

I suspect that ISODD() and ISEVEN() didn't exist in Excel 2000, so instead, you could test whether the Match value was either 0 or 1, in Mod 2.

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

1

0

1

3

5

7

10

11

2

0

5

10

12

14

3

0

2

8

10

12

4

0

2

5

7

13

14

5

0

4

5

Sheet1

Kishan · Aug 3, 2018

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

StephenCrump said:
If you change your helper cells to cumulative totals, you could do it like this:

Conditionally formatted green using formula:
=AND(COLUMNS($A1:A1)<=MAX($P1:$X1),ISODD(MATCH(COLUMNS($A1:A1)-1,$P1:$X1,1)))

Conditionally formatted red using formula:
=AND(COLUMNS($A1:A1)<=MAX($P1:$X1),ISEVEN(MATCH(COLUMNS($A1:A1)-1,$P1:$X1,1)))

I suspect that ISODD() and ISEVEN() didn't exist in Excel 2000, so instead, you could test whether the Match value was either 0 or 1, in Mod 2.

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X

1
1
3
5
7
10
11

2
5
10
12
14

3
2
8
10
12

4
2
5
7
13
14

5
4
5

<TBODY>
</TBODY>

Hi StephenCrump, thank you for the CF formulas, as you imagine ISODD() and ISEVEN does not exists in excel 2000.

Kind Regards,
Kishan

Kishan · Aug 3, 2018

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

Hi, I tried to modified MickG code to adjust for my necessity, I could set it partially it does not colour the "1X2" sequence as I need.
The code below format as shown...

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

2

3

4

5

C1

C2

C3

C4

C5

C6

C7

C8

C9

C10

C11

C12

C13

C14

6

X

1

2

1

2

X

1

2

X

1

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

1

X

1

2

1

2

1

2

X

2

X

15

X

2

X

1

X

1

X

1

X

16

1

X

1

2

X

17

1

2

1

2

X

1

2

X

2

1

X

18

1

X

1

2

X

1

X

1

X

1

19

1

X

1

2

1

2

1

X

1

20

1

X

1

X

1

21

X

1

2

X

1

2

X

1

2

X

22

1

X

1

X

1

X

1

23

1

X

2

1

2

24

1

2

X

1

25

1

2

1

X

1

26

1

X

1

2

1

27

X

1

X

1

2

X

1

28

X

2

1

2

1

X

1

X

29

1

X

2

1

2

X

1

X

30

2

1

X

1

X

1

X

1

31

Sheet5

Code:

Sub MG25Jul32()

    Range("A6:P30").Select
    Selection.Interior.ColorIndex = xlNone
    Selection.Font.ColorIndex = 1
    Range("C6").Select


Dim Rng As Range
Dim Dn As Range
Dim Ac As Integer
Dim n As Integer
Dim Temp As String
Dim r As Integer
Dim p As Integer
Dim num As Long, col As Variant
Set Rng = Range(Range("C6"), Range("C" & Rows.Count).End(xlUp))


col = Array(10, 3)


For Each Dn In Rng
    ReDim ray(1 To 14, 1 To 2)
    Temp = Dn
    n = 1
        For Ac = 1 To 14
            If Not Dn(, Ac) = Temp Then
                n = n + 1
                Temp = Dn(, Ac)
            End If
    ray(n, 1) = Dn(, Ac): ray(n, 2) = ray(n, 2) + 1
Next Ac
p = 3
For Ac = 1 To n
    For r = p To 29
        
        If Not ray(Ac, 1) = Cells(6 + i, r) Then
        
        Else
        Cells(Dn.Row, r).Resize(, ray(Ac, 2)).Interior.ColorIndex = col(num)
        Cells(Dn.Row, r).Resize(, ray(Ac, 2)).Font.ColorIndex = 2
          
          p = r
          Exit For
       End If
    
    Next r
    
    If ray(Ac, 2) = 1 Then GoTo a
    
    num = num + 1
    num = IIf(num = 2, 0, num)
a:


    num = num + 1
    num = IIf(num = 2, 0, num)
Next Ac


i = i + 1
    
Next Dn


End Sub

But I need to have colouring like this... for more detail please see the post#1

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

2

3

4

5

C1

C2

C3

C4

C5

C6

C7

C8

C9

C10

C11

C12

C13

C14

6

X

1

2

1

2

X

1

2

X

1

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

1

X

1

2

1

2

1

2

X

2

X

15

X

2

X

1

X

1

X

1

X

16

1

X

1

2

X

17

1

2

1

2

X

1

2

X

2

1

X

18

1

X

1

2

X

1

X

1

X

1

19

1

X

1

2

1

2

1

X

1

20

1

X

1

X

1

21

X

1

2

X

1

2

X

1

2

X

22

1

X

1

X

1

X

1

23

1

X

2

1

2

24

1

2

X

1

25

1

2

1

X

1

26

1

X

1

2

1

27

X

1

X

1

2

X

1

28

X

2

1

2

1

X

1

X

29

1

X

2

1

2

X

1

X

30

2

1

X

1

X

1

X

1

31

32

Sheet6

Thank you in advance

Regards,
Kishan

MickG · Aug 3, 2018

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

Try this:-

Code:

[COLOR="Navy"]Sub[/COLOR] MG03Aug57
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] RngAc [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, Fd [COLOR="Navy"]As[/COLOR] Boolean
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C6"), Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    col = vbGreen
    Dn.Interior.Color = col
    [COLOR="Navy"]For[/COLOR] Ac = 1 To 13
        Fd = False
        Txt = Dn.Offset(, Ac - 1) & "," & Dn.Offset(, Ac)
        [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Txt
            [COLOR="Navy"]Case[/COLOR] "X,1": Fd = True
            [COLOR="Navy"]Case[/COLOR] "2,1": Fd = True
            [COLOR="Navy"]Case[/COLOR] "2,X": Fd = True
      [COLOR="Navy"]End[/COLOR] Select
[COLOR="Navy"]If[/COLOR] Fd [COLOR="Navy"]Then[/COLOR]
    col = IIf(col = vbGreen, vbRed, vbGreen)
[COLOR="Navy"]End[/COLOR] If
    Dn.Offset(, Ac).Interior.Color = col
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Kishan · Aug 3, 2018

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

MickG said:

Try this:-

Code:

[COLOR=navy]Sub[/COLOR] MG03Aug57
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, Num [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Txt [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] col [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] RngAc [COLOR=navy]As[/COLOR] Range, R [COLOR=navy]As[/COLOR] Range, Fd [COLOR=navy]As[/COLOR] Boolean
[COLOR=navy]Set[/COLOR] Rng = Range(Range("C6"), Range("C" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    col = vbGreen
    Dn.Interior.Color = col
    [COLOR=navy]For[/COLOR] Ac = 1 To 13
        Fd = False
        Txt = Dn.Offset(, Ac - 1) & "," & Dn.Offset(, Ac)
        [COLOR=navy]Select[/COLOR] [COLOR=navy]Case[/COLOR] Txt
            [COLOR=navy]Case[/COLOR] "X,1": Fd = True
            [COLOR=navy]Case[/COLOR] "2,1": Fd = True
            [COLOR=navy]Case[/COLOR] "2,X": Fd = True
      [COLOR=navy]End[/COLOR] Select
[COLOR=navy]If[/COLOR] Fd [COLOR=navy]Then[/COLOR]
    col = IIf(col = vbGreen, vbRed, vbGreen)
[COLOR=navy]End[/COLOR] If
    Dn.Offset(, Ac).Interior.Color = col
[COLOR=navy]Next[/COLOR] Ac
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Mick, it is an absolutely fine, code it is colouring the "1X2" sequence without having the helper columns. Checked with my long data worked awfully.

Warmly thank to you for solving all complicated issues.

Mike, Stay Blessed 

Have a Good Weekend

Kind regards,
Kishan

MickG · Aug 4, 2018

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

You're welcome

nikio8 · Aug 4, 2018

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

Hi your logic is quite confusing.

you should say something like:
each row is separate function and started green

series are 1111XXX2222, different colour what the previous series were

after the last 2, change colour on 1 or X.

if series has not started:
-the first value is X,
-or the first value after 2 is X it is still series and should be highlighted with different colour

this is nearly there, just need to account for double X and double 2s at the end of a series

Code:

Sub coloring()

Dim colr, r, c As Integer
Dim startS, endS, previousIsX, previousIs2 As Boolean
Range("A2:N15").Interior.ColorIndex = 0
colr = 4 '4 green, 3 red
startS = False
endS = False


For r = 2 To 15
    For c = 1 To 14
        If Cells(r, c).Value = 1 Then
            'if series did not start, begin
            If startS = False Then startS = True
            previousIsX = False
            previousIs2 = False
            
        ElseIf Cells(r, c).Value = 2 Then
            If startS And Not previousIs2 Then endS = True
            
            previousIsX = False
            previousIs2 = True
            
        ElseIf Cells(r, c).Value = "X" Then
            If Not startS Then 'Or Not previousIsX Then
                startS = True
                endS = True
            End If


            previousIsX = True
        End If




        Cells(r, c).Interior.ColorIndex = colr
        
        'if both conditions met, change color
        If startS And endS Then
            startS = False
            endS = False
            
            If colr = 4 Then
                colr = 3
            Else
                colr = 4
            End If
                        
        End If
        
        
    Next c


Next r




End Sub

nikio8 · Aug 5, 2018

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

Hi, darn did not see the second page. Interesting puzzle.
Here is my solution.
As above the main change occurs after you reach 1 then 2 ; and then 1 again (new series) or X (new invalid X series). This is where color is changed.

If someone can explain why i get error type mismatch when using "Function changeColor(ii As Integer) As Integer", maybe time is not wasted? I have marked colr as integer and using changeColor(colr).

Code:

Option Explicit


Sub coloring()


Dim colr, r, c As Integer
Dim newSeries, previousIs2 As Boolean
Range("A2:N15").Interior.ColorIndex = 0


For r = 2 To 15
    colr = 4 '4 green, 3 red
    newSeries = False
    previousIs2 = False
    
    For c = 1 To 14
        Select Case Cells(r, c).Value
        Case 1
            If newSeries = False Then
                newSeries = True
                If c > 1 Then colr =[B] changeColor(colr)[/B]
            End If
            
            previousIs2 = False


            
        Case 2
            If newSeries = True Then
                newSeries = False
            End If
            


            previousIs2 = True


            
        Case "X"
            If previousIs2 And Not newSeries Then
                colr = [B]changeColor(colr)[/B]
            End If
        
 
            previousIs2 = False


        End Select
        
        Cells(r, c).Interior.ColorIndex = colr
        
        
    Next c


Next r




End Sub


Function changeColor(ii As [B]Variant[/B]) As Integer
    If ii = 3 Then
        ii = 4
    Else
        ii = 3
    End If
    changeColor = ii
End Function

Special conditional formatting; highlight the series

Kishan

Well-known Member

Kishan

Well-known Member

Excel Facts

Kishan

Well-known Member

StephenCrump

MrExcel MVP

Kishan

Well-known Member

Kishan

Well-known Member

MickG

MrExcel MVP

Kishan

Well-known Member

MickG

MrExcel MVP

nikio8

Board Regular

nikio8

Board Regular

Similar threads

Forum statistics

Share this page

Special conditional formatting; highlight the series

Well-known Member

Well-known Member

Excel Facts

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Board Regular

Board Regular

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