Replies

Very good point Steve.

Very good point Steve.

OOps! sorry I did not see that H8 on your stat sheet is not a simple value, if you Paste Special / Values from H8 stat sheet to H8 data sheet, H3 data sheet becomes CF’d as you want.

Also I found that conditional formats cannot refer to other worksheets or workbooks, therefore you cannot make the CF of H3 on the data sheet depend on H1 and H8 on the stat sheet (which would be messy anyway).

This may need the attention of a VB Expert.

OOps! sorry I did not see that H8 on your stat sheet is not a simple value, if you Paste Special / Values from H8 stat sheet to H8 data sheet, H3 data sheet becomes CF’d as you want.

Also I found that conditional formats cannot refer to other worksheets or workbooks, therefore you cannot make the CF of H3 on the data sheet depend on H1 and H8 on the stat sheet (which would be messy anyway).

This may need the attention of a VB Expert.

The formula for CF of H3 on your stat sheet refers to cells H1, H3, and H8 also on your stat sheet. The values in these cells fulfil the conditions, so H3 is formatted accordingly.

The formula for CF of H3 on your data sheet refers to cells H1, H3 and H8 on your data sheet, H1 and H8 are blank on your data sheet so the conditions are not met and therefore H3 is not formatted the way you want. If you copy cells H1 and H8 from your stat sheet to H1 and H8 in your data sheet you will find H3 on your data sheet does become formatted the way you want.

To do what you want, that is to capture the format of H3 and paste it somewhere else, effectively turning it from conditional formatting to ‘straight’ formatting is much more difficult, and I cannot see a way of doing it, though possibly via VBA…

The formula for CF of H3 on your stat sheet refers to cells H1, H3, and H8 also on your stat sheet. The values in these cells fulfil the conditions, so H3 is formatted accordingly.

The formula for CF of H3 on your data sheet refers to cells H1, H3 and H8 on your data sheet, H1 and H8 are blank on your data sheet so the conditions are not met and therefore H3 is not formatted the way you want. If you copy cells H1 and H8 from your stat sheet to H1 and H8 in your data sheet you will find H3 on your data sheet does become formatted the way you want.

To do what you want, that is to capture the format of H3 and paste it somewhere else, effectively turning it from conditional formatting to ‘straight’ formatting is much more difficult, and I cannot see a way of doing it, though possibly via VBA…

The formatting is being copied across, look at the formatting via Format Cells / Font and Borders, it’s the same on both sheets.

The conditional formatting can also be copied across using Paste, but then the CF on your data sheet refers to the cells on your data sheet, which do not fulfil the conditions. You will have to change the CF formulas to refer to your stat sheet to get it to work.

The formatting is being copied across, look at the formatting via Format Cells / Font and Borders, it’s the same on both sheets.

The conditional formatting can also be copied across using Paste, but then the CF on your data sheet refers to the cells on your data sheet, which do not fulfil the conditions. You will have to change the CF formulas to refer to your stat sheet to get it to work.

Word (2k) has a customisable grid (Drawing Toolbar / Grid…) that can be used for aligning drawings. Lines etc normally snap to grid corners, use the Alt key to disable this.

Word (2k) has a customisable grid (Drawing Toolbar / Grid…) that can be used for aligning drawings. Lines etc normally snap to grid corners, use the Alt key to disable this.

Steve

As Beryl wants to remove the second row where there are duplicates you must compare each cell with the one above it if you are going to remove TRUEs, so you should start =A2=A1 in cell B2 not in cell B1. See attached.

Also a good idea to replace Col B with values only otherwise you have confusing #REFs in Col b after removing the TRUEs.

Steve

As Beryl wants to remove the second row where there are duplicates you must compare each cell with the one above it if you are going to remove TRUEs, so you should start =A2=A1 in cell B2 not in cell B1. See attached.

Also a good idea to replace Col B with values only otherwise you have confusing #REFs in Col b after removing the TRUEs.

Another possiblity is to colour alternate rows. The following macro does this (most of the code is to avoid losing the cell grey borders):

Sub AltRowsGreenGreyBorders()
‘
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
“=MOD(ROW(),2)=1”
Selection.FormatConditions(1).Interior.ColorIndex = 35
‘
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
If Selection.Columns.Count > 1 Then
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
End If
If Selection.Rows.Count > 1 Then
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
End If

End Sub

The following macro clears the colouring:

Sub ClearFormats()
‘
Selection.ClearFormats
End Sub

Another possiblity is to colour alternate rows. The following macro does this (most of the code is to avoid losing the cell grey borders):

Sub AltRowsGreenGreyBorders()
‘
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
“=MOD(ROW(),2)=1”
Selection.FormatConditions(1).Interior.ColorIndex = 35
‘
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
If Selection.Columns.Count > 1 Then
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
End If
If Selection.Rows.Count > 1 Then
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
End If

End Sub

The following macro clears the colouring:

Sub ClearFormats()
‘
Selection.ClearFormats
End Sub

Have a look at This Thread and This Thread and That Thread