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

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

supressing display of duplicates

electricpete

Electrical

Recommended for you

palusa

Petroleum

palusa

Petroleum

Similar threads

Part and Inventory Search

Sponsor