Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Color alternating rows (2k)

    OK i'm pretty sure this was covered, but my searches came up empty, so here's what i'm trying to do - i have a table with sets of data that go down the rows, looking like this:

    Set 1
    item one
    item two
    item three
    Set 2
    item four
    item jezza
    item six
    Set 3
    item seven
    ...
    ...
    .. etc etc

    So i want the rows that don't say Set X alternately colored... but above the Set X's there's some header info in bold as well that i don't want alternately colored, so really if i could use the bold identifier as a row i DON'T want colored, i guess that would be the best. i attempted to make a macro for it but got confused trying to figure out how to identify the sections to perform the operation on. you don't need to write the macro for it, i think i can get it, i just need to know what to look for in a row to not color it.

    Thanks!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Color alternating rows (2k)

    You can check if the (entire) content of a cell is bold as follows:

    If Range("A37").Font.Bold Then
    ' code to execute if cell content is bold
    Else
    ' code to execute if cell content is not bold
    End If

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Color alternating rows (2k)

    John

    Without code you can use conditional formatting:

    1. Select A1:B10 (for example), ensuring the A1 is the active cell.
    2. Go to Format>Conditional Formatting
    3. Select Formula is: and then add this formula: =Left($A1,3)="Set"
    4. Now click the Format button and then the Font tab and then select Bold from Font Style:
    5. Now click OK, then OK again

    This should now set the cells with Set as the starting word as bold
    Jerry

  4. #4
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color alternating rows (2k)

    Thanks guys, i'll try it out when i get back into work tomorrow morning <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Color alternating rows (2k)

    I found that it can be a <img src=/S/oink.gif border=0 alt=oink width=15 height=15> sometimes <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> so you can alwats use the format painter to drag down.

    I have used this method for Total and Sub-Total so I use =right($A1,5)="Total" in the past, so I would play around with it.
    Jerry

  6. #6
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color alternating rows (2k)

    i tried it out actually, and it gave me the result i wanted, but having to do it 28 times was annoying after a while! i'd rather click the macro once to do the whole spreadsheet and since this is something i do quite often, i think i'll just write a macro up for it. thanks though jerry... the format painter is a good alternative method for this, especially with fewer sets of data.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Color alternating rows (2k)

    OK it was late last night, so whilst waiting for an installation this afternoon I wrote this for you:

    Sub ChangeHeading()
    Range("A1:E144").Select

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=LEFT($A1,3)=""Set"""
    With Selection.FormatConditions(1).Font
    .Bold = True
    .Italic = False
    End With
    Selection.FormatConditions(1).Interior.ColorIndex = 35
    End Sub

    Change the Range to suit your purpose. I wrote it to go bold and give a green background...hope this helps
    Jerry

  8. #8
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color alternating rows (2k)

    does that color alternating rows though? or all the rows?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Color alternating rows (2k)

    Umm No. It just changes the Header for the Set row.

    The coding for that will take a little longer as I am tied up for a little while at work

    I suppose you could use conditional formatting but that again is a fiddle. If you want that trick

    Select Conditional Formatting

    Formula Is: =MOD(ROW(),2) and change the Format to the colour you want.This will colur every alternate row.
    Jerry

  10. #10
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color alternating rows (2k)

    i was thinking of using a boolean and changing its value after every row..... then depending on that i color it or i don't color it. i have some old macro i think Hans wrote for me that uses that method. mod would work too though.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Color alternating rows (2k)

    You could use something like this:

    Sub Colorize()
    Dim i As Long
    Dim lngMaxRow As Long
    Dim blnColor As Boolean
    lngMaxRow = Range("A65536").End(xlUp).Row
    For i = 1 To lngMaxRow
    If Range("A" & i).Font.Bold Then
    ' reset boolean
    blnColor = False
    Else
    blnColor = Not blnColor
    End If
    If blnColor Then
    Range("A" & i).EntireRow.Interior.ColorIndex = 24
    Else
    Range("A" & i).EntireRow.Interior.ColorIndex = xlColorIndexNone
    End If
    Next i
    End Sub

    It assumes that the cells to look at are in column A.

  12. #12
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Color alternating rows (2k)

    John

    This is not perfect but it uses a mixture of AutoFormatting and my code, maybe you could tweak it a little, but it was an off the wall idea a minute ago

    Sub ChangeHeading()

    On Error Resume Next

    Range("A1:E144").Select


    Selection.AutoFormat Format:=xlRangeAutoFormatList1, Number:=True, Font:= _
    True, Alignment:=True, Border:=True, Pattern:=True, Width:=True

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=LEFT($A1,3)=""Set"""
    With Selection.FormatConditions(1).Font
    .Bold = True
    End With
    Selection.FormatConditions(1).Interior.ColorIndex = 35

    End Sub

    I typed in your data and then ran it and I got a data table with grey/white alternates and each Set X went green...Good Luck
    Jerry

  13. #13
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color alternating rows (2k)

    That'll do it. Thanks, Hans.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  14. #14
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color alternating rows (2k)

    Jerry you actually gave me some help on some other stuff i wanted to do but i didn't know the syntax for <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Thanks!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  15. #15
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color alternating rows (2k)

    Hans, i tried adding a line of code to it so that it made the rows that were font.bold into a different color... but it didn't work:

    Sub Colorize()
    Dim i As Long
    Dim lngMaxRow As Long
    Dim blnColor As Boolean
    lngMaxRow = Range("A65536").End(xlUp).Row
    For i = 1 To lngMaxRow
    If Range("A" & i).Font.Bold Then
    Range("A" & i).EntireRow.Interior.ColorIndex = 34
    ' reset boolean
    blnColor = False
    Else
    blnColor = Not blnColor
    End If
    If blnColor Then
    Range("A" & i).EntireRow.Interior.ColorIndex = 24
    Else
    Range("A" & i).EntireRow.Interior.ColorIndex = xlColorIndexNone
    End If
    Next i
    End Sub

    why doesn't that change the row's who have font.bold = true? ALSO, where can you find excel's color index list?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •