Conditional Formating question

[durbat2]

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

 

[T. Valko]

Try this...

Assume your data is in the range A215

Select the range A315
Goto the menu Format>Conditional 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

 

[Mike H]

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

 

[T. Valko]

I left out some important info!

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

 

[ShaneDevenshire]

Hi,

First a slight variation on Valko's suggestion:

1. Select all the cells starting in A2100
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.