Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    highlight a line with a interval (excel)

    im making a spreadsheet which ill be pasting information in for a school project. I want to make a simple template to make it look good after I paste my information in but need to know how to make a macro that will highlight in yellow every 9th line in yellow from column E to N, and below it a formula calculates a interval from cells within the border which is a seperate trial.. I have attach a xls to show what i mean. A border every 10 rows would seperate the trials. Is there a way to do this in excel. The number of trials is undetermened but im guessin a hundered but could change. hope im making sense,.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: highlight a line with a interval (excel)

    A macro to do this would not be a big problem. However, do you really need one? Why not just set up A1:N1 the way you want. Then select A1:N10, and grab the fill handle in cell N10 and drag down as far as you need?
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: highlight a line with a interval (excel)

    thanks, I think I had a brain fart and forgot the ease of stuff
    thanks

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: highlight a line with a interval (excel)

    I would like to take this a little further.

    I often use Conditional formatting to highlight alternate rows in a list! In the Conditional Formats Dialog I type: =MOD(ROW(),2)=1, and this returns True and False that shades the rows alternately! Is it at all possible to create an expression that will return False 8 times and then a True? If so, it can be used in the Conditional Format Feature, and it will not be necessary to maintain the shading if rows are inserted/deleted! Any ideas!
    Regards,
    Rudi

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: highlight a line with a interval (excel)

    Something like:
    <pre>=MOD(ROW(),9)=1</pre>


    will have 1 row formatted and 8 rows unformatted...

    <pre>=MOD(ROW(),9)<>1</pre>


    will have 1 row unformatted and 8 rows formatted...

    I think this will give you ideas for other patterns...
    Steve

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: highlight a line with a interval (excel)

    Ha ha... as simple as that! I wonder whats happening to my brain...may need a <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22>

    Thanx Steve.

    PS: I changed the =1 to =mod(row(),9)=0 Then it works on each 9th row! This will be better to use in Seaner's case!
    Cheers
    Regards,
    Rudi

  7. #7
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: highlight a line with a interval (excel)

    Ya That might work a little better, How would I implent this? Format conditional formating, then every 9th highlight in light yellow?

    better yet, I have been try this little macro:

    Range("D8:M8").Select
    With Selection.Interior
    .ColorIndex = 36
    .Pattern = xlSolid
    End With
    End Sub

    If I want this baby to fire when ever Column D says: Temp Gradient (C/M). I dont know much about making a loop with a condition.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: highlight a line with a interval (excel)

    <P ID="edit" class=small>(Edited by sdckapr on 30-May-05 13:41. Added some additional comments)</P>1) for the conditional format for your example (Start at row 7 and get every ninth one: 7, 16, 25, 34, etc)
    Highlight A1:N55
    Format - conditional formatting...
    Choose: Formula is
    =MOD(ROW(),9)=7
    [format...]
    Patterns(tab)
    Select the light yellow
    [ok][ok]

    2) if you don't want the pattern, you could check for the phrase with conditional formatting:
    Highlight A1:N55
    Format - conditional formatting...
    Choose: Formula is
    =$D1="Temp Gradient (C/M)"
    [format...]
    Patterns(tab)
    Select the light yellow
    [ok][ok]

    Both 1 and 2 will be automatic

    3) for the macro, this can be run to format the sheet. It goes into a module and you would run it whenever you wanted to highlight the rows with the phrase in the col d (it does not change the formatting if it is not the phrase)
    <pre>Option Explicit
    Sub HighlightLines()
    Dim sSearch As String
    Dim sAddress As String
    Dim rng As Range
    Dim rCell As Range
    sSearch = "Temp Gradient (C/M)"
    Set rng = Range(Range("d1"), Range("D65536").End(xlUp))
    With rng
    Set rCell = .Find(sSearch, LookIn:=xlValues)
    If Not rCell Is Nothing Then
    sAddress = rCell.Address
    Do
    With rCell.EntireRow.Range("A1:N1").Interior
    .ColorIndex = 36
    .Pattern = xlSolid
    End With
    Set rCell = .FindNext(rCell)
    Loop While Not rCell Is Nothing _
    And rCell.Address <> sAddress
    End If
    End With
    Set rCell = Nothing
    Set rng = Nothing
    End Sub</pre>


    #3 is not automatic it will have to be rerun whenever changes are made

    4) If you want an automatic macro you could do something like this:
    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCell As Range
    If Not Intersect(Target, Range("D")) Is Nothing Then
    For Each rCell In Intersect(Target, Range("D"))
    If rCell.Value = "Temp Gradient (C/M)" Then
    With rCell.EntireRow.Range("A1:N1").Interior
    .ColorIndex = 36
    .Pattern = xlSolid
    End With
    Else
    rCell.EntireRow.Range("A1:N1"). _
    Interior.ColorIndex = xlNone
    End If
    Next
    End If
    Set rCell = Nothing
    End Sub</pre>


    The above goes into the sheet object (not a normal module) and it will run when a change is made to the sheet. if the change is in Col D and it is the phrase it will format the "row" as desired. If it is not the phrase it will clear the formatting. This macro may clear the clipboard and will "prevent" undo...

    Steve

  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: highlight a line with a interval (excel)

    Thank you Steve

    in the sheet object do I place it in add?

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: highlight a line with a interval (excel)

    I do not understand the question, but will make a guess.

    #1 and #2 are 2 different ways to do conditional formatting

    #3 is macro that goes into a module

    #4 is a macro that goes into (in your example sheet) the sheet1 object (right click on the sheet1 tab in excel and select "view code"). Add the macro to this "pane" in VB

    Steve

  11. #11
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: highlight a line with a interval (excel)

    Thanks, never knew that

Posting Permissions

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