Questions

Conditional Formatting - Heavy boarder to the bottom every fourth row

Tags:
+
0 Votes
Locked

Conditional Formatting - Heavy boarder to the bottom every fourth row

pbennett
I am designing a multi-page sign-in sheet that I would like to have the bottom line of every fourth row formated to be a heavy line to stand out from the rest to facilitate "time-in"/"-out" entries being written on the form.

The sheet has pre-printed names, one for each row. These change on a daily basis and are updated via "cut & paste" from another excel sheet.

In addition, the form has 12 "in"/"out" columns per row and I would like to apply 10% shading each of the 6 "out" columns.


I would like to be able to cut and paste new names onto the sheet or add / delete rows without having to reformat the cells all the time. I believe that this can be done by using conditional formatting but do not know what formula to use.

I am using Excel 2003.

Can anyone help?
  • +
    0 Votes
    LocoLobo

    But what if you just copy the cells that are already formatted then paste - special - formatting?

    Hopefully someone else has a better way to do the conditional formatting. Here's what I did. In cells BZ2:BZ33 I repeated the sequence 1,2,3,4. I arbitrarily chose cell C2 as my starting area. Click cell C2, go to Format - Conditional Formatting.

    Condition 1 = Formula is

    In the box next to it I typed =$I2=4

    Now drag down from cell C2 to cell e33. This worked for me in Excel 2003. It is klugy.

    +
    0 Votes
    pbennett

    Thanks for your suggestion.
    I tried as you suggested but couldn't get it to work. In any case, as I understand it, your solution would not maintain cell formatting if rows were added or deleted because the info in the BZ column would changed accordingly.

    Thanks again!

    Therefore...
    I'm still looking for a solution that will work even though rows are added or deleted.

    +
    0 Votes
    LocoLobo

    It's still not perfect. Maybe someone more knowledgable can drop a hint here.

    Cells F3:H10 are the starting range.

    In cell aa3 or another out of sight place type in the formula =row(f3). Make sure to use the starting row for your range. In this case you should get a 3.

    Select cell F3 (the upper left cell of your range). Go to Format - Conditional Formatting. Under the drop down choose 'Formula is'. In the formula area type in '=MOD(ROW(F3:F10)-($AA$3-1),4)=0'. Choose your format (bottom border). Copy the format to the rest of your range. If you can figure out a better formula to change the Row Value of the first row of your range to 1 let me know. That's the klugy part of this setup.

    This is working for me with Excel 2003. You can delete rows (except the first row), insert rows, move the entire range, etc. Basically you need a mod function set up so the first row is always 1.

    +
    0 Votes
    pbennett

    Thanks. I got this working to format every four rows as selected in the conditional formatting window. I am able it add and delete as you indicated while maintaining the cell formatting.

    However, there is one small problem... The formatting window under conditional formatting does not give you the option to change the weight of the line. I can change the color. I can change the style to various dashed lines but I can't make it a "heavy" line. Any suggestions???

    Thanks!

    +
    0 Votes
    LocoLobo

    I noticed that too. Excel 2007 is supposed to have more conditional formatting features. Try this article:

    'http://msdn2.microsoft.com/en-us/library/bb226688(office.11).aspx'

    I just skimmed it because we won't upgrade for a while. I'm not sure Excel 2007 will solve your problems either. One thing we wanted years ago (Excel 97) was numeric formatting under conditional formats. But Excel 2003 doesn't have them either. Excel 2007 might.

    Good luck

  • +
    0 Votes
    LocoLobo

    But what if you just copy the cells that are already formatted then paste - special - formatting?

    Hopefully someone else has a better way to do the conditional formatting. Here's what I did. In cells BZ2:BZ33 I repeated the sequence 1,2,3,4. I arbitrarily chose cell C2 as my starting area. Click cell C2, go to Format - Conditional Formatting.

    Condition 1 = Formula is

    In the box next to it I typed =$I2=4

    Now drag down from cell C2 to cell e33. This worked for me in Excel 2003. It is klugy.

    +
    0 Votes
    pbennett

    Thanks for your suggestion.
    I tried as you suggested but couldn't get it to work. In any case, as I understand it, your solution would not maintain cell formatting if rows were added or deleted because the info in the BZ column would changed accordingly.

    Thanks again!

    Therefore...
    I'm still looking for a solution that will work even though rows are added or deleted.

    +
    0 Votes
    LocoLobo

    It's still not perfect. Maybe someone more knowledgable can drop a hint here.

    Cells F3:H10 are the starting range.

    In cell aa3 or another out of sight place type in the formula =row(f3). Make sure to use the starting row for your range. In this case you should get a 3.

    Select cell F3 (the upper left cell of your range). Go to Format - Conditional Formatting. Under the drop down choose 'Formula is'. In the formula area type in '=MOD(ROW(F3:F10)-($AA$3-1),4)=0'. Choose your format (bottom border). Copy the format to the rest of your range. If you can figure out a better formula to change the Row Value of the first row of your range to 1 let me know. That's the klugy part of this setup.

    This is working for me with Excel 2003. You can delete rows (except the first row), insert rows, move the entire range, etc. Basically you need a mod function set up so the first row is always 1.

    +
    0 Votes
    pbennett

    Thanks. I got this working to format every four rows as selected in the conditional formatting window. I am able it add and delete as you indicated while maintaining the cell formatting.

    However, there is one small problem... The formatting window under conditional formatting does not give you the option to change the weight of the line. I can change the color. I can change the style to various dashed lines but I can't make it a "heavy" line. Any suggestions???

    Thanks!

    +
    0 Votes
    LocoLobo

    I noticed that too. Excel 2007 is supposed to have more conditional formatting features. Try this article:

    'http://msdn2.microsoft.com/en-us/library/bb226688(office.11).aspx'

    I just skimmed it because we won't upgrade for a while. I'm not sure Excel 2007 will solve your problems either. One thing we wanted years ago (Excel 97) was numeric formatting under conditional formats. But Excel 2003 doesn't have them either. Excel 2007 might.

    Good luck