Alternate colouring of rows based on cell value?

C

chuckles1066

Banned
Joined
Dec 20, 2004
Messages
372
Afternoon all,

I have a spreadsheet with data populating columns A thru BK.

The data is sorted by column A, a five digit number. There might be only one row with a particular five digit number, there might be six or seven.

What I would like to be able to do is alternately colour each row when the value in column A changes,

So at the start of the data, column A in row 2 has a five digit number. This row might be coloured light grey. I need something to test if column A in row 3 contains the same value as row 2 - if it does, the row needs to be coloured light grey as well, if not it needs to be coloured light yellow.

And so on until the end of the data.

I only need columns A thru BK coloured.

I'm sure it's easy for the geniuses (shouldn't that be "geni-i"?) on here but I'm struggling.

Thanks as always.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
Give this a go:

Code: 
Public Sub AltColor()
Dim i           As Long, _
    LR          As Long, _
    CurrColor   As Long
    
CurrColor = 15
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:BK2").Interior.ColorIndex = CurrColor
 
Application.ScreenUpdating = False
 
For i = 3 To LR
    If Range("A" & i).Value <> Range("A" & i - 1).Value Then
        If CurrColor = 15 Then
            CurrColor = 36
        Else
            CurrColor = 15
        End If
    End If
    Range("A" & i & ":BK" & i).Interior.ColorIndex = CurrColor
Next i
 
Application.ScreenUpdating = True
        
End Sub
 
Upvote 0
An ingenuous approach could be:
-in a free colum, for example in BL2, use the fomula =IF(BL2=BL1,BL1,BL1+1)
-copy this formula in the cells below BL2
-then select A2:BKxxx a nd set a conditonal format
--"the formula is"
--use the formula =Mod($BL2,2)=1
--assign the cell background that you prefer
--Ok
-Ok

HTH, bye


PS: don't miss the massage from MrKowz, above
 
Last edited:
Upvote 0
If you are working in excel 2007 or later you can set up conditional formatting based on a formula.

With that you have to manually input the formula, but it could be something like this:

in A4: =if(a2=a3,true,false)
then if that condition (a2=a3) is met, the formatting you set will be applied.

I
 
Upvote 0
If you are working in excel 2007 or later you can set up conditional formatting based on a formula.

With that you have to manually input the formula, but it could be something like this:

in A4: =if(a2=a3,true,false)
then if that condition (a2=a3) is met, the formatting you set will be applied.

I don't
 
Upvote 0
MrKowz said:
Give this a go:

Code: 
Public Sub AltColor()
Dim i           As Long, _
    LR          As Long, _
    CurrColor   As Long
    
CurrColor = 15
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:BK2").Interior.ColorIndex = CurrColor
 
Application.ScreenUpdating = False
 
For i = 3 To LR
    If Range("A" & i).Value <> Range("A" & i - 1).Value Then
        If CurrColor = 15 Then
            CurrColor = 36
        Else
            CurrColor = 15
        End If
    End If
    Range("A" & i & ":BK" & i).Interior.ColorIndex = CurrColor
Next i
 
Application.ScreenUpdating = True
        
End Sub
Click to expand...

I used this and it worked perfectly, but I have a followup question. I add row groups of data with consecutive identifiers (1, 2, 3...) in A2-500 throughout each month. If I delete data in a row that is already highlighted, the formatting stays behind.

How can I change that row back to the default color if the identifier in column A is deleted?
 
Upvote 0
Re: Alternate colouring of rows based on [relative] cell value?

Hiya! sorry if i'm not supposed to do this, I'm new. this seems very close to what i am trying to do, but I can't get any of these solutions to work for me and i was hoping that someone would know how to do this without VBA.
I want to alternate row colors at each change in the value of a specified column in a list of numbers sorted ascending, kinda like the using the "subtotal" tool, but instead of summing at each change in [column name] i want to change the row color. i want it to say "fill the rows with color#1 until the value changes then switch to color#2, then at the next value change switch back to color#1, and so on through the end of the data. i found a solution that nearly works, but it doesn't alternate the colors when there are two groups of odd or even numbers occurring consecutively or something... this solution is a conditional format using two rules formatting for different colors, one of the following formulas for each rule:
=EVEN(VALUE(RIGHT($B1,LEN($B1)-1)))=VALUE(RIGHT($B1,LEN($B1)-1))
=NOT(EVEN(VALUE(RIGHT($B1,LEN($B1)-1)))=VALUE(RIGHT($B1,LEN($B1)-1)))

here's a short example of what i get using this solution - notice where the number changes but the colors stay the same. that is the problem, the need to alternate at every change reliably. I wish i had bookmarked that posting so I could ask the amazing person who came up with these giant formulas what they would do. live and learn i guess

<table style="border-collapse: collapse; width: 56pt;" border="0" cellpadding="0" cellspacing="0" width="74"><col style="width: 56pt;" width="74"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl71" style="border: 0.5pt solid windowtext; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 15pt; width: 56pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20" width="74">300090</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="border: 0.5pt solid windowtext; background: rgb(194, 214, 154) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300091</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="border: 0.5pt solid windowtext; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300092</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="border: 0.5pt solid windowtext; background: rgb(194, 214, 154) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300093</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="border: 0.5pt solid windowtext; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300094</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl73" style="border: 0.5pt solid windowtext; background: rgb(194, 214, 154) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300103</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="border: 0.5pt solid windowtext; background: rgb(194, 214, 154) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300103</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="border: 0.5pt solid windowtext; background: rgb(194, 214, 154) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300103</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl73" style="border: 0.5pt solid windowtext; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300104</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl74" style="border: 0.5pt solid windowtext; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300104</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl74" style="border: 0.5pt solid windowtext; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300104</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl73" style="border: 0.5pt solid windowtext; background: rgb(194, 214, 154) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300105</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="border: 0.5pt solid windowtext; background: rgb(194, 214, 154) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300105</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="border: 0.5pt solid windowtext; background: rgb(194, 214, 154) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300105</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="border: 0.5pt solid windowtext; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300106</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl75" style="border: 0.5pt solid windowtext; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 700; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300106</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl75" style="border: 0.5pt solid windowtext; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 700; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300106</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="border: 0.5pt solid windowtext; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300108</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="border: 0.5pt solid windowtext; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300108</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="border: 0.5pt solid windowtext; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300108</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl73" style="border: 0.5pt solid windowtext; background: rgb(194, 214, 154) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300119</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="border: 0.5pt solid windowtext; background: rgb(194, 214, 154) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300119</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="border: 0.5pt solid windowtext; background: rgb(194, 214, 154) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300119</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="border: 0.5pt solid windowtext; background: rgb(194, 214, 154) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300151</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="border: 0.5pt solid windowtext; background: rgb(194, 214, 154) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300151</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="border: 0.5pt solid windowtext; background: rgb(194, 214, 154) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300151</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="border: 0.5pt solid windowtext; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300152</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="border: 0.5pt solid windowtext; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300152</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="border: 0.5pt solid windowtext; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300152</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="border: 0.5pt solid windowtext; background: rgb(194, 214, 154) none repeat scroll 0% 0%; height: 15pt; font-size: 10pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="20">300155</td> </tr> </tbody></table>
it would be nice to have a robust solution that would format regardless of the sort order (as long as it wouldn't make the file run slow) but i would be very happy with a solution that depended on sorting if it was reliable.

thanks in advance and sorry if this should have been a new thread.
 
Upvote 0
Re: Alternate colouring of rows based on [relative] cell value?

jetbd said:
Hiya! sorry if i'm not supposed to do this, I'm new. this seems very close to what i am trying to do, but I can't get any of these solutions to work for me and i was hoping that someone would know how to do this without VBA.
I want to alternate row colors at each change in the value of a specified column in a list of numbers sorted ascending, kinda like the using the "subtotal" tool, but instead of summing at each change in [column name] i want to change the row color. i want it to say "fill the rows with color#1 until the value changes then switch to color#2, then at the next value change switch back to color#1, and so on through the end of the data. i found a solution that nearly works, but it doesn't alternate the colors when there are two groups of odd or even numbers occurring consecutively or something... this solution is a conditional format using two rules formatting for different colors, one of the following formulas for each rule:
=EVEN(VALUE(RIGHT($B1,LEN($B1)-1)))=VALUE(RIGHT($B1,LEN($B1)-1))
=NOT(EVEN(VALUE(RIGHT($B1,LEN($B1)-1)))=VALUE(RIGHT($B1,LEN($B1)-1)))

here's a short example of what i get using this solution - notice where the number changes but the colors stay the same. that is the problem, the need to alternate at every change reliably. I wish i had bookmarked that posting so I could ask the amazing person who came up with these giant formulas what they would do. live and learn i guess

<TABLE style="WIDTH: 56pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=74 border=0><COLGROUP><COL style="WIDTH: 56pt" width=74><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(184,204,228); BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 56pt; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" width=74 height=20>300090</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(194,214,154); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300091</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(184,204,228); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300092</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(194,214,154); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300093</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(184,204,228); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300094</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(194,214,154); BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300103</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(194,214,154); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300103</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(194,214,154); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300103</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(184,204,228); BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300104</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(184,204,228); BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300104</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(184,204,228); BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300104</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(194,214,154); BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300105</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(194,214,154); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300105</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(194,214,154); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300105</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(184,204,228); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300106</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: rgb(184,204,228); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300106</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: rgb(184,204,228); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300106</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(184,204,228); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300108</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(184,204,228); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300108</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(184,204,228); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300108</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(194,214,154); BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300119</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(194,214,154); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300119</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(194,214,154); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300119</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(194,214,154); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300151</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(194,214,154); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300151</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(194,214,154); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300151</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(184,204,228); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300152</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(184,204,228); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300152</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(184,204,228); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300152</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: rgb(194,214,154); BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 15pt; TEXT-DECORATION: none; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous" height=20>300155</TD></TR></TBODY></TABLE>
it would be nice to have a robust solution that would format regardless of the sort order (as long as it wouldn't make the file run slow) but i would be very happy with a solution that depended on sorting if it was reliable.

thanks in advance and sorry if this should have been a new thread.
Click to expand...
How many rows of data are there?

What version of Excel are you using?
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Filling alternate cells with two colours?
Replies
3
Views
211
Joe4
Joe4
D
  • Question
Continuous cell colouring
Replies
1
Views
246
Joe4
Joe4
A
  • Question
VBA to change cell colour based on content
Replies
3
Views
219
Snakehips
Snakehips
T
  • Question
VBA Formatting Rows
Replies
7
Views
236
dreid1011
dreid1011
SaraWitch
  • Solved
Change cell colour based on multiple values
Replies
4
Views
409
awoohaw
awoohaw

Forum statistics

Threads
1,215,409
Messages
6,124,733
Members
449,185
Latest member
hopkinsr

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