supressing display of duplicates

electricpete · May 14, 2004

Is there a way to supress display of cells that are the same as cell directly above.

I can do it on a single column with conditional formatting by selecting column range for example B2:10 and settin format to zero if condition1 is cell = B1. Apparently it treats B1 as relative cell reference compared to first cell on the range. If B4 is same as B5, then B5 is supressed (that's what I want).

Now the problem, I would like to do it over a block of data several columns wide. Conditional formatting won't let me do it. If I select B2:F10, and try to test for equal to B1:F1, it gives me error message

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

palusa · May 18, 2004

electricpete,

try this VB macro (to be inserted in a new module):

Sub Macro3()
'
' Extends Conditional Formatting to multiple columns
'
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=R[-1]C"
Selection.FormatConditions(1).Font.ColorIndex = 0
End Sub

After selecting B2:F10 (as per your example) run the macro (Alt+F8 -Macro3 - Run).
Hopefully it should work.

Hope it helps.

_LF

PS: Associating the macro to a button on the sheet and running it, resulted in an error ("Unable to set the ColorIndex property of the Font class"). Does anybody know why?

palusa · May 18, 2004

Errata Corrige:

Last two lines of Macro3 are:

(...)
Selection.FormatConditions(1).Font.ColorIndex = 2
End Sub

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

supressing display of duplicates

electricpete

Electrical

palusa

Petroleum

palusa

Petroleum

Similar threads

Part and Inventory Search

Sponsor