Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    (custom)'autoformat' according to changes in col1? (97)

    I want to make a list easier to read. Normally I use autoformat. But I don't want every 2nd row or whatever shaded. I want the rows to be shaded depending on the data in col 1.
    Frinstance if cells a1+a2 contained similar data then I want rows 1 + 2 to be formatted together, if a3:a5 contain similar data, then I want rows 3-5 formatted together & so on.

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

    Re: (custom)'autoformat' according to changes in col1? (97)

    What do you mean by 'similar'?

    If you want to shade the rows according to some kind of grouping of the data in the first column, you can use conditional formatting. For instance: if the entry in column 1 is negative, use red shading; if the entry is between 0 and 100, use yellow shading, and if the entry is above 100, use green shading.

    If you want to consecutive rows to have the same shading if the cells in the first column have the same value, you might use a macro like the following:

    Sub MyAutoFormat()
    Const conColor1 = 35
    Const conColor2 = 36
    Dim rng As Range
    Dim lngRow As Long
    Set rng = Selection
    If rng.Rows.Count = 1 And rng.Columns.Count = 1 Then
    Set rng = rng.CurrentRegion
    End If
    rng.Rows(1).Interior.ColorIndex = conColor1
    For lngRow = 2 To rng.Rows.Count
    If rng.Cells(lngRow, 1) = rng.Cells(lngRow - 1, 1) Then
    rng.Rows(lngRow).Interior.ColorIndex = rng.Rows(lngRow - 1).Interior.ColorIndex
    Else
    rng.Rows(lngRow).Interior.ColorIndex = conColor1 + conColor2 - rng.Rows(lngRow - 1).Interior.ColorIndex
    End If
    Next lngRow
    End Sub

    This will apply a variation on alternating shading. See the attached workbook.

    If you mean something else entirely, post back.
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: (custom)'autoformat' according to changes in col1? (97)

    Thanks Hans... Nice macro-

    ps You were correct to guess that I meant "similar values".

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: (custom)'autoformat' according to changes in col1? (97)

    <P ID="edit" class=small>(Edited by macropod on 20-Aug-02 13:00. )</P>Another way to do this is with conditional formatting. For example, if your reference data is in column A, starting at row2, you could use the "Formula Is" conditional format in A2 with:
    =OR($A1=$A2,$A3=$A2)
    and set the colour to whatever you want it to be whenever this condition is true. Having done this in A2, copy it and do a Paste Special|Format for the range you want to apply the conditional formatting to. If you want to shade A1 as well, the conditional format formula on that row would be:
    =$A1=$A2

    No macros/macro security to worry about with this approach, plus it updates automatically and you have greater control over how many columns get shaded.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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