supressing display of duplicates
supressing display of duplicates
(OP)
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
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.
Talk To Other Members
RE: supressing display of duplicates
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?
RE: supressing display of duplicates
Last two lines of Macro3 are:
(...)
Selection.FormatConditions(1).Font.ColorIndex = 2
End Sub