script help

[acw]

Nude

Using conditional formatting as you have started put in
the following:

Condition 1 formula is
=AND(COUNTIF(A:A,A1)>1,COUNTIF($A$1:A1,A1)=1)
and format however.
Condition 2 formula is
=COUNTIF(A:A,A1)>1
and format differently.

Copy down accordingly. Update ranges to work for B-D
accordingly.

Tony

 

[Matthew Connor]

Im sorry, I am not catching on here, it isnt working how i am formatting it

lets imagine one of the column range is need is B10:B500 how would i format
it, and can I just select the whole range, go to conditional formatting and
do it?

I can modify Tony's solution fo you. For the specific range of
B10:B500, you would select the range B10:B500. Note what the active
cell is (the whole range is highlighted but one cell is where the
'cursor' is - where you would enter information if you were to type
something). For this example, B10 is the active cell. If B10 isn't you
would need to change any lone B10 references to that cell (but not the
B10:B500 references).

First conditional format:
=AND(COUNTIF(B10:B500,B10)>1,COUNTIF($B$10:B10,B10)=1)
(use the Format... button to select a 'first occurance of duplicates'
format)

Second conditional format:
=COUNTIF(B10:B500,B10)>1
(use the Format... button to select a 'duplicate occurance' format)

You can refer to my earlier message for a more step-by-step walkthru
for the conditional format steps.

Now, this formatting would not look for duplicates past line 500.
Tony's solution refered to the entire column - so when data is added
past line 500 it would be part of the lookup range. (To change to
Tony's solution, change B10:B500 to B:B.)

Finally, if people are pasting data in from other worksheets, this
format will likely be lost as people will copy their formats along
with their values when they copy-paste. People can Edit | Paste
Special | Values(option) to avoid this, but people forget.

I'm putting together a event macro for you that won't have these
limitations.


Matthew

 

[Matthew Connor]

I can modify Tony's solution fo you. For the specific range of B10:B500,
you would select the range B10:B500. Note what the active cell is (the
whole range is highlighted but one cell is where the 'cursor' is - where
you would enter information if you were to type something). For this
example, B10 is the active cell. If B10 isn't you would need to change
any lone B10 references to that cell (but not the B10:B500 references).

First conditional format:
=AND(COUNTIF(B10:B500,B10)>1,COUNTIF($B$10:B10,B10)=1)
(use the Format... button to select a 'first occurance of duplicates'
format)

Second conditional format:
=COUNTIF(B10:B500,B10)>1
(use the Format... button to select a 'duplicate occurance' format)

You can refer to my earlier message for a more step-by-step walkthru for
the conditional format steps.

Now, this formatting would not look for duplicates past line 500. Tony's
solution refered to the entire column - so when data is added past line
500 it would be part of the lookup range. (To change to Tony's solution,
change B10:B500 to B:B.)

Finally, if people are pasting data in from other worksheets, this
format will likely be lost as people will copy their formats along with
their values when they copy-paste. People can Edit | Paste Special |
Values(option) to avoid this, but people forget.

I'm putting together a event macro for you that won't have these
limitations.


Matthew

Perhaps not the best event macro, but it has the benefit of working.
You can goto Tools | Macros | Visual Basic Editor to pull up the macro
editor. Then open the Sheet page in the Project Explorer and paste
this in. You can change the line
Const DATA_RANGE = "B10:E500"
to suit you needs.

Good luck,

Matthew

Private Sub Worksheet_Change(ByVal Target As Range)
Const DATA_RANGE = "B10:E500"
Dim rngColumn As Range
Dim rngFound As Range
Dim blnTargetFirst As Boolean
Dim blnMultiplesFound As Boolean
Dim intLastFoundRow

If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Not Application.Intersect(Target, Range(DATA_RANGE)) Is
Nothing Then
Application.EnableEvents = False
For Each rngColumn In Range(DATA_RANGE).Columns
If Not Application.Intersect(Target, rngColumn) Is
Nothing Then
Set rngFound = rngColumn.Find(Target.Value)
blnTargetFirst = True
Do
If Not rngFound.Address = Target.Address Then
If blnTargetFirst Then
blnTargetFirst = False
rngFound.Interior.Color = RGB(0, 255, 0)
Else
rngFound.Interior.Color = RGB(255, 0, 0)
End If
blnMultiplesFound = True
End If
intLastFoundRow = rngFound.Row
Set rngFound = rngColumn.Find(Target.Value, rngFound)
Loop Until rngFound.Row <= intLastFoundRow
End If
If blnTargetFirst And blnMultiplesFound Then
Target.Interior.Color = RGB(0, 255, 0)
ElseIf blnMultiplesFound Then
Target.Interior.Color = RGB(255, 0, 0)
Else
Target.Interior.Pattern = xlPatternNone
End If
Next
Application.EnableEvents = True
End If
End Sub

 

[Matthew Connor]

when i do this i get an error any time i put something in a cell

compile error - syntax error, and this line is highlighted..
***************************************************
If Not Application.Intersect(Target, Range(DATA_RANGE)) Is
**************************************************

<much reply history snipped>

It has to do with word-wrapping. That line should continue (on the
same line) with
Nothing Then.

The full line is:
"If Not Application.Intersect(Target, Range(DATA_RANGE)) Is Nothing Then"
(again, all on one line in the programming module)

Below, I see that two lines were affected by word-wrapping. It may be
different when you view it. Both of the affect lines had the "Nothing
Then" wrapped onto the next line.

As a reminder, you will need to make sure you put the code in the
correct place. In the Project Explorer (in the Visual Basic Editor,
use Ctrl-R or View |Project Explorer to display it if you can't find
it), you can open your workbook and sheet a listing of each of your
worksheets as:
Sheet1 (SomeName)
Sheet2 (AnotherName)
Sheet3 (Output)
ThisWorkbook

You will need to double-click to open the sheet you want this code to
execute for. When you do you should see the name reflected in the
title bar.

Good luck,

Matthew