Ignore Zeroes With Selection

sherwinarae

Board Regular
Joined
Jul 28, 2003
Messages
73
Does anyone know how to ignore 0.00 from the following?
In other words, do not apply the below formatting to cells that have a value of 0.00?

Many thanks!!!

With Selection
.FormatConditions.Delete
.FormatConditions.AddUniqueValues
.FormatConditions(1).DupeUnique = xlDuplicate
.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End With
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Perhaps this?

With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=0"
.FormatConditions(1).StopIfTrue = True
.FormatConditions.AddUniqueValues
.FormatConditions(2).DupeUnique = xlDuplicate
.FormatConditions(2).Interior.Color = RGB(255, 0, 0)
.FormatConditions(2).StopIfTrue = False
End With
 
Upvote 0
YES!!!!!!!!!!!!!!!! It worked!!! Thank you so so much!!!! one last step, would it be possible to populate cell H5 with the word "duplicates" if any duplicates were found with the original selection?
 
Upvote 0
YES!!!!!!!!!!!!!!!! It worked!!! Thank you so so much!!!! one last step, would it be possible to populate cell H5 with the word "duplicates" if any duplicates were found with the original selection?



Tried this but it didn't work lol:


Dim dupindicator As Range
Set dupindicator = Range("H5")

'highlight duplicates
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=0"
.FormatConditions(1).StopIfTrue = True
.FormatConditions.AddUniqueValues
.FormatConditions(2).DupeUnique = xlDuplicate
.FormatConditions(2).Interior.Color = RGB(255, 0, 0) And dupindicator.Value = "duplicates"
.FormatConditions(2).StopIfTrue = False
 
Upvote 0
Hi

You can't do it with conditional formatting - the code needs to add a formula in H5. Try this - I have assumed you want to ignore zeroes when it determines if there are duplicates or not.

Code:
Sub test2()
    ActiveWorkbook.Names.Add Name:="MySelection", RefersTo:=Selection
    Range("H5").FormulaArray = "=IF(SUM(1/COUNTIF(MySelection,MySelection))-(COUNTIF(MySelection,0)>0)-COUNTIF(MySelection,"">0"")<>0,""Duplicates"",""No duplicates"")"
    With Selection
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=0"
        .FormatConditions(1).StopIfTrue = True
        .FormatConditions.AddUniqueValues
        .FormatConditions(2).DupeUnique = xlDuplicate
        .FormatConditions(2).Interior.Color = RGB(255, 0, 0)
        .FormatConditions(2).StopIfTrue = False
    End With
End Sub
Regards

Murray
 
Upvote 0
Hi Murray,


Thank you so much! It almost worked lol.
Well, on the first tab it works, however i have several tabs (i should have mentioned that)
which i need to apply this exact same logic to.
So yes it works correctly on the first tab however when you get to the second, third, and further tabs it seems to be referencing the value we are storing as "MySelection".


Here's the code (Tab 1 & Tab 2 are labeled)





Sub MacroFPAOEEEXP()
ActiveWorkbook.Names.Add Name:="MySelection", RefersTo:=Selection


'
' MacroFPAOEEEXP Macro
'


Sheets("OEENEW").Select
Range("A3:X3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents


Windows("OEE NEW").Activate
Range("A33:X33").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("FPA").Activate
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


Windows("OEE NEW").Activate
ChDir "C:\Users\FINANCE\FPA\OE&E"
ActiveWorkbook.Save
ActiveWindow.Close


'(TAB 1 PASTE MONTHLY ACTUALS - JO OTHER)


Windows("FPA").Activate

Sheets("JO OTHER").Select
Range("AI147").Select
Selection.End(xlToRight).Select
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(535, 1)).Select
Selection.Formula = "=SUMIFS(OEENEW!$R:$R,OEENEW!$X:$X,""JUDGES"",OEENEW!$A:$A,$C147)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'highlight duplicates
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=0"
.FormatConditions(1).StopIfTrue = True
.FormatConditions.AddUniqueValues
.FormatConditions(2).DupeUnique = xlDuplicate
.FormatConditions(2).Interior.Color = RGB(255, 0, 0)
.FormatConditions(2).StopIfTrue = False

End With


Range("H5").FormulaArray = "=IF(SUM(1/COUNTIF(MySelection,MySelection))-(COUNTIF(MySelection,0)>0)-COUNTIF(MySelection,"">0"")<>0,""Duplicates"",""No duplicates"")"
Range("H5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Application.CutCopyMode = False


'(TAB 2 PASTE MONTHLY ACTUALS - EXEC)




Sheets("EXEC").Select
Range("AI147").Select
Selection.End(xlToRight).Select
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(535, 1)).Select
Selection.Formula = "=SUMIFS(OEENEW!$R:$R,OEENEW!$X:$X,""EXECUTIVE"",OEENEW!$A:$A,$C147)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


'highlight duplicates
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=0"
.FormatConditions(1).StopIfTrue = True
.FormatConditions.AddUniqueValues
.FormatConditions(2).DupeUnique = xlDuplicate
.FormatConditions(2).Interior.Color = RGB(255, 0, 0)
.FormatConditions(2).StopIfTrue = False
End With


Range("H5").FormulaArray = "=IF(SUM(1/COUNTIF(MySelection,MySelection))-(COUNTIF(MySelection,0)>0)-COUNTIF(MySelection,"">0"")<>0,""Duplicates"",""No duplicates"")"
Range("H5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Application.CutCopyMode = False



Application.CutCopyMode = False
 
Upvote 0
If I've understood correctly then don't put this line:

ActiveWorkbook.Names.Add Name:="MySelection", RefersTo:=Selection

at the beginning where you have it.
Instead, put it before these two lines wherever it is needed (i.e. twice in this case)

'highlight duplicates
With Selection

Regards

Murray
 
Upvote 0
Tried that as well, I also tested the formula it seems to always populate with "duplicates" even when the selection contains all zeroes. I think it may be best if i email you the spreadsheet?
 
Upvote 0
For completeness for future users:

Range("H5").Formula = "=IF(SUMPRODUCT((MySelection<>0)*(COUNTIF(MySelection,MySelection&"""")>1))>0,""Duplicates"",""No duplicates"")"

Regards

Murray
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top