Colour cells through VBA

[motilulla]

Hello,

I am looking VBA solution, which can fill the colours if lower line data match with upper line using alternate colourers

For example:
Row3 data match with row2 fill colour red and font white
Row4 data match with row3 fill colour green and font white

I am using conditional format formula =SI(C2=C3;1;0) for row 3 for CF red and white
I am using conditional format formula =SI(C3=C4;1;0) for row 4 for CF green and white and then copy this format to row down

But know I am getting message formatting will not save too much data so I cannot continue formatting more that 1800 rows

So please I need the VBA solution

Sample Data

Book1
	B	C	D	E	F	G	H	I	J	K	L
1		Za1	Za1	Za1	Za1	Za1	Za1	Za1	Za1	Za1
2		A	5	Y	5	Y	A	A	5	5
3		5	A	Y	Y	Y	5	A	A	Y
4		5	A	5	5	A	A	Y	Y	A
5		5	A	5	Y	Y	5	A	5	5
6		A	5	A	5	A	5	5	A	5
7		A	A	A	5	5	5	5	A	5
8		Y	A	5	5	5	Y	A	A	A
9		A	5	A	A	5	5	5	5	5
10		5	A	A	5	Y	5	Y	5	5
11		A	Y	A	5	5	A	5	5	5
12		5	5	5	5	5	5	A	A	A
13		5	Y	5	Y	A	5	5	Y	A
14		5	A	A	5	5	Y	A	A	5
15		5	A	A	5	5	Y	5	5	Y
16		5	5	5	A	5	5	5	5	A
17		A	5	Y	A	5	5	5	Y	A
18		5	5	5	A	5	A	A	5	A
19		5	5	5	5	5	A	A	Y	5
20		5	5	5	5	5	5	5	5	5
21		5	Y	Y	5	5	5	5	5	5
22		5	5	A	5	5	5	5	5	5
23		A	A	A	5	5	5	A	5	5
24		A	Y	5	5	Y	5	5	5	5
25		5	A	Y	Y	5	5	5	Y	A
26		Y	5	A	A	5	5	A	A	5
27		5	A	5	Y	Y	5	5	5	5
28		5	5	A	5	Y	Y	Y	5	5
29		Y	5	A	5	A	5	A	5	5
30		A	5	5	5	5	5	Y	5	Y
31		5	Y	5	5	A	5	5	5	A
32		A	5	5	5	5	A	5	Y	5
33
Sheet1

Thank you all
Excel 2000
Regards,
Moti

 

[My Aswer Is This]

Try this:

Sub Color_Me()
'Modified 1-26-18 12:51 AM EST
Application.ScreenUpdating = False
Dim c As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
    
    For Each c In Range("C2:K" & Lastrow)
        If c.Row Mod 2 = 0 Then If c.Offset(-1, 0).Value = c.Value Then c.Interior.ColorIndex = 4: c.Font.Color = vbWhite
        If c.Row Mod 2 = 1 Then If c.Offset(-1, 0).Value = c.Value Then c.Interior.ColorIndex = 3: c.Font.Color = vbWhite
    Next
Application.ScreenUpdating = True
End Sub

 

[motilulla]

Try this:

Sub Color_Me()
'Modified 1-26-18 12:51 AM EST
Application.ScreenUpdating = False
Dim c As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
    
    For Each c In Range("C2:K" & Lastrow)
        If c.Row Mod 2 = 0 Then If c.Offset(-1, 0).Value = c.Value Then c.Interior.ColorIndex = 4: c.Font.Color = vbWhite
        If c.Row Mod 2 = 1 Then If c.Offset(-1, 0).Value = c.Value Then c.Interior.ColorIndex = 3: c.Font.Color = vbWhite
    Next
Application.ScreenUpdating = True
End Sub

My Aswer Is This, this is exactly I was looking for, working as request prefect!

Thank you very much for your kind help

Have a good day

Regards,
Moti

 

[Rick Rothstein]

You probably do not have enough rows of data to be able to see the time difference, but the following non-looping macro executes in about two-thirds the time of your current macro...

[table="width: 500"]
[tr]
	[td]Sub ColorIfEqualToAbove()
  Application.ScreenUpdating = False
  Application.ReplaceFormat.Clear
  With Range("C1").CurrentRegion.Offset(1)
    .Resize(.Rows.Count - 1).Resize(.Rows.Count - 1).Value = Evaluate(Replace(Replace("IF(@2=@1,IF(MOD(ROW(@2),2),""|""&@2,""-""&@2),@2)", "@1", .Offset(-1).Address), "@2", .Address))
    Application.ReplaceFormat.Interior.Color = 255
    .Replace "|", "", xlPart, , , , False, True
    .Replace "|", "", xlPart, , , , False, False
    Application.ReplaceFormat.Interior.Color = 32768
    .Replace "-", "", xlPart, , , , False, True
    .Replace "-", "", xlPart, , , , False, False
  End With
  Application.ReplaceFormat.Clear
  Application.ScreenUpdating = False
End Sub[/td]
[/tr]
[/table]

 

[My Aswer Is This]

Glad I was able to help you. Hope you try reading the script and can learn some from it. You have a nice day also.QUOTE=motilulla;4995405]My Aswer Is This, this is exactly I was looking for, working as request prefect!

Thank you very much for your kind help

Have a good day

Regards,
Moti[/QUOTE]

 

[motilulla]

You probably do not have enough rows of data to be able to see the time difference, but the following non-looping macro executes in about two-thirds the time of your current macro...

Hello Rick Rothstein, thank you I tried the code and getting following message and highlighted the part of code is shown in red

Complication Error

The number of arguments is incorrect or the property assignment is not valid

.Replace "|", "", xlPart, , , , False, False 

Please could you check?

Regards,
Moti

 

[motilulla]

.Replace "|", "", xlPart, , , , False, False 

Sorry Rick, I just noticed that I copied wrong line

It is the line below one.

 .Replace "|", "", xlPart, , , , False, True

Regards,
Moti

 

[Rick Rothstein]

Hello Rick Rothstein, thank you I tried the code and getting following message and highlighted the part of code is shown in red

Complication Error

The number of arguments is incorrect or the property assignment is not valid

.Replace "|", "", xlPart, , , , False, False 

Please could you check?

I tested the code before I posted it and it worked fine then. To be sure, I just tested it again and it still works fine. I am not sure what to tell you as I cannot duplicate the problem you are having. Could you post a copy of your workbook to DropBox so that we can download it and test our code directly against your actual data rather than the made up example we are using now? If you can post it, make sure it has your raw data the way it is before any attempt is made to color it.

 

[My Aswer Is This]

In your previous post you said:
"

this is exactly I was looking for, working as request prefect​

"

So if the code I provided worked great. Did you now fine a problem with it now?

 

[motilulla]

I tested the code before I posted it and it worked fine then. To be sure, I just tested it again and it still works fine. I am not sure what to tell you as I cannot duplicate the problem you are having. Could you post a copy of your workbook to DropBox so that we can download it and test our code directly against your actual data rather than the made up example we are using now? If you can post it, make sure it has your raw data the way it is before any attempt is made to color it.

Hello Rick, thank you for helping and giving options, I trust it is not a code, as far as I guess it is version problem.

Regards,
Moti