highlight unique values in a column except "x" and "pp"

[Victtor]

I have numerical data in column F from F4 to F33. I have data also in column F from F35 to F50


I need the data in F35:F50 highlited if it does not appear in F4:F33


I will use the code or formula you provide to apply to the same cells in other columns.


for example if cell F35 has "2", but it there is no "2" in F4:F33, F35 should be highlighted. I understand that this part is easy, but I need the formula to omit empty cells and cells with "x" or "pp" in them.

Thanks in advance

 

[VBA Geek]

if inderstand correctly this should be your conditional format rule

=AND(COUNTIF($F$4:$F$33,F35)=0,F35<>"",F35<>"pp",F35<>"x")

 

[njimack]

=AND(COUNTIF($F$4:$F$33,F35)=0,ISERROR(SEARCH("x",F35)),ISERROR(SEARCH("pp",F35)))

 

[Victtor]

if inderstand correctly this should be your conditional format rule

=AND(COUNTIF($F$4:$F$33,F35)=0,F35<>"",F35<>"pp",F35<>"x")

Ok,thanks, need a little more tweaking. This highlights cells with duplicate values, I need only the cells in F35:F50 highlited if that is a unique value

 

[Victtor]

=AND(COUNTIF($F$4:$F$33,F35)=0,ISERROR(SEARCH("x",F35)),ISERROR(SEARCH("pp",F35)))

disregard second reply

 

[VBA Geek]

Ok,thanks, need a little more tweaking. This highlights cells with duplicate values, I need only the cells in F35:F50 highlited if that is a unique value

i missed that part, try this

=AND(COUNTIF($F$4:$F$33,F34)=0,F34<>"",F34<>"pp",F34<>"x",COUNTIF($F$35:$F$50,F35)=1)

 

[Victtor]

i missed that part, try this

=AND(COUNTIF($F$4:$F$33,F34)=0,F34<>"",F34<>"pp",F34<>"x",COUNTIF($F$35:$F$50,F35)=1)

So, what should be happening is that the nonempty cells F35-F50 should be highlighted because they should start out as unique values. As I input values in cells F4-F34 (that equal the unique values in F34-F50), The values in F34-F50 should "unhighlight" because they are no longer unique values.

For example: There is a "1" in F40 (This should automatically be highlighted since it is a unique value). If I use that "1" on, say, cell F16, then F40 should "unhighlight" because it is no longer a unique value

 

[VBA Geek]

maybe post a sample of your data with expected results to avoid confusion?

 

[Victtor]

maybe post a sample of your data with expected results to avoid confusion?

Still working on a way to post data. Meanwhile....

Ok, I got this far with just using Excel tools (conditional formatting):

Sub ExampleTest()

Columns("F:F").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlUnique
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub


I need to do 2 things with this:

1. Write this code to evaluate each column separately (Columns F through AG), meaning I can have a "1" in Column F and Column G and they both be considered unique values for their columns
2. Perhaps make a combo button to toggle this formatting on and off?

Any help would be appreciated and thanks in advance