Special conditional formatting; highlight the series

K

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
Using Excel 2000</SPAN></SPAN>

Hi,</SPAN></SPAN>

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

My series is made of three characters 1, X and 2</SPAN></SPAN>

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

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"</SPAN></SPAN>

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

Result data example</SPAN></SPAN>


Book1
ABCDEFGHIJKLMNOPQ
1
2
3
4
5P1P2P3P4P5P6P7P8P9P10P11P12P13P14
6X1212XX1121X22
71X2221XX22XX11
81X11XX22XX11XX
91X1221X11X1221
10X1X1X11X111XX1
11XXX1111X1X212X
122X1112XXX11121
13X1XX1111111X11
14211X211X2X222X
15X112X12111111X
161212X21X12X111
172XX1221112111X
18111121112121XX
191212111221211X
20
21
Sheet2


Thank you in advance</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 
Last edited:
Sort by date Sort by votes
Re: Need help with a special conditional formatting; highlight the series

Any luck with this query
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Re: Need help with a special conditional formatting; highlight the series

Bump
 
Upvote 0
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
ABCDEFGHIJKLMNOPQRSTUVWX
1013571011
205101214
30281012
402571314
5045
Sheet1
 
Last edited:
Upvote 0
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

<TBODY>
[TD="align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"]3
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"]5
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"]7
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"]10
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"]11
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]

[TD="align: center"]2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"]5
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"]10
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"]12
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"]14
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]

[TD="align: center"]3
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"]2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"]8
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"]10
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"]12
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]

[TD="align: center"]4
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"]2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"]5
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"]7
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"]13
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"]14
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]

[TD="align: center"]5
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"]4
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"]5
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"][/TD]

</TBODY>
Click to expand...
Hi StephenCrump, thank you for the CF formulas, as you imagine ISODD() and ISEVEN does not exists in excel 2000.</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 
Upvote 0
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
ABCDEFGHIJKLMNOPQ
1
2
3
4
5C1C2C3C4C5C6C7C8C9C10C11C12C13C14
6X1212XX112X112
71X2221XX22XX11
81X11XX22XX11XX
91X1221X11X1221
10X1X1X11X111XX1
11XXX1111X1X212X
122X1112XXX11121
13X1XX1111111X11
141XX12121112X2X
15X2X11X1111X11X
16111111XXX1112X
171212X112X2111X
181XX112XX1X11X1
191XX11211221X11
20111X1111X11111
21X12X1112X112XX
22111X1XX1X11111
2311111XX2111112
2411111111112XX1
2512211111111X11
2611X11111111121
27XXX111X112X111
28X211211111XX1X
291X221112XX111X
3021XX11XX1X1111
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
ABCDEFGHIJKLMNOPQ
1
2
3
4
5C1C2C3C4C5C6C7C8C9C10C11C12C13C14
6X1212XX112X112
71X2221XX22XX11
81X11XX22XX11XX
91X1221X11X1221
10X1X1X11X111XX1
11XXX1111X1X212X
122X1112XXX11121
13X1XX1111111X11
141XX12121112X2X
15X2X11X1111X11X
16111111XXX1112X
171212X112X2111X
181XX112XX1X11X1
191XX11211221X11
20111X1111X11111
21X12X1112X112XX
22111X1XX1X11111
2311111XX2111112
2411111111112XX1
2512211111111X11
2611X11111111121
27XXX111X112X111
28X211211111XX1X
291X221112XX111X
3021XX11XX1X1111
31
32
Sheet6


Thank you in advance

Regards,
Kishan
 
Last edited:
Upvote 0
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
 
Upvote 0
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
Click to expand...
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.</SPAN></SPAN>

Warmly thank to you for solving all complicated issues.</SPAN></SPAN>

Mike, Stay Blessed </SPAN></SPAN>

Have a Good Weekend</SPAN></SPAN>

Kind regards,</SPAN></SPAN>
Kishan :) </SPAN></SPAN>
 
Upvote 0
Re: Need help with a special conditional formatting; highlight the series

You're welcome
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Highlighting duplicate consecutive cells by row
Replies
2
Views
484
kvsrinivasamurthy
K
F
  • Solved
Exponential Colour Filling for Graph
Replies
7
Views
380
FaithMonax
F
M
  • Solved
Conditional format match highlight in red font
Replies
3
Views
168
kevin9999
K
A
  • Question Question
Conditional Formatting but not copying it as such
Replies
1
Views
395
t0ny84
T
C
  • Question Question
conditional formatting based on multiple criteria
2
Replies
13
Views
406
dreid1011
dreid1011

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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