I have a spread sheet logging production on 3 shifts. I need to
automatically draw a line in the sheet every time the shift changes
like this

A B C D
Shift Part No Qty shift total
0
a ive 0533 23
a ive 9080 45
a ive 4838 13 81
b ive 4838 45
b ive 9937 23 68
c ive 9937 47
c ive 9937 23
c rvi 1715 23
c rvi 1715 25 118
a rvi 1715 13
a rvi 1715 15 28
b rvi 0444 35
b ive 7661 23
b ive 7661 14 72

A line under all 4 columns everytime the entry in col A differs from
the previous one i.e. when the shift changes

Thanks
Martin

Try this...

Assume your data is in the range A2:D15

Select the range A3:D15
Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=$A3<$A2
Click the Format button
Select the Borders tab
Select the desired line style (not much of a selection!)
OK out

--
Biff
Microsoft Excel MVP


" wrote in message
...
I have a spread sheet logging production on 3 shifts. I need to
automatically draw a line in the sheet every time the shift changes
like this

A B C D
Shift Part No Qty shift total
0
a ive 0533 23
a ive 9080 45
a ive 4838 13 81
b ive 4838 45
b ive 9937 23 68
c ive 9937 47
c ive 9937 23
c rvi 1715 23
c rvi 1715 25 118
a rvi 1715 13
a rvi 1715 15 28
b rvi 0444 35
b ive 7661 23
b ive 7661 14 72

A line under all 4 columns everytime the entry in col A differs from
the previous one i.e. when the shift changes

Thanks
Martin

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub Marine()
Dim MyRange As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
If c.Value < c.Offset(1, 0).Value Then
Set c = c.Resize(, 4) '4 columns change to suit
With c.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
End With

End If
Next
End Sub

Mike

" wrote:

I have a spread sheet logging production on 3 shifts. I need to
automatically draw a line in the sheet every time the shift changes
like this

A B C D
Shift Part No Qty shift total
0
a ive 0533 23
a ive 9080 45
a ive 4838 13 81
b ive 4838 45
b ive 9937 23 68
c ive 9937 47
c ive 9937 23
c rvi 1715 23
c rvi 1715 25 118
a rvi 1715 13
a rvi 1715 15 28
b rvi 0444 35
b ive 7661 23
b ive 7661 14 72

A line under all 4 columns everytime the entry in col A differs from
the previous one i.e. when the shift changes

Thanks
Martin

I left out some important info!

On the Borders tab, you want to set the line to be at the *top* of the cell.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this...

Assume your data is in the range A2:D15

Select the range A3:D15
Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=$A3<$A2
Click the Format button
Select the Borders tab
Select the desired line style (not much of a selection!)
OK out

--
Biff
Microsoft Excel MVP


" wrote in message
...
I have a spread sheet logging production on 3 shifts. I need to
automatically draw a line in the sheet every time the shift changes
like this

A B C D
Shift Part No Qty shift total
0
a ive 0533 23
a ive 9080 45
a ive 4838 13 81
b ive 4838 45
b ive 9937 23 68
c ive 9937 47
c ive 9937 23
c rvi 1715 23
c rvi 1715 25 118
a rvi 1715 13
a rvi 1715 15 28
b rvi 0444 35
b ive 7661 23
b ive 7661 14 72

A line under all 4 columns everytime the entry in col A differs from
the previous one i.e. when the shift changes

Thanks
Martin

Hi,

First a slight variation on Valko's suggestion:

1. Select all the cells starting in A2:D100
2. Choose Format, Conditional Formatting
3. Pick Formula is from the first drop down and enter the formula
=$A2<$A3
4. Click Format and choose the Borders tab
5. Pick a Style and a Color
6. Click the Bottom border button, bottom left side. Click OK twice.

In 2007:
1. Select the range as above
2. Choose Home, Conditional Formatting, New Rule
3. Choose Use a formula to determine which cells to format
4. In the Format values where this formula is true enter
=$A2<$A3
5. Click Format, and on the Border tab choose a Style and Color
6. Click the Bottom border button, and then OK twice.

--
Thanks,
Shane Devenshire


" wrote:

I have a spread sheet logging production on 3 shifts. I need to
automatically draw a line in the sheet every time the shift changes
like this

A B C D
Shift Part No Qty shift total
0
a ive 0533 23
a ive 9080 45
a ive 4838 13 81
b ive 4838 45
b ive 9937 23 68
c ive 9937 47
c ive 9937 23
c rvi 1715 23
c rvi 1715 25 118
a rvi 1715 13
a rvi 1715 15 28
b rvi 0444 35
b ive 7661 23
b ive 7661 14 72

A line under all 4 columns everytime the entry in col A differs from
the previous one i.e. when the shift changes

Thanks
Martin