Conditional Formatting & CountIf problem

  • Thread starter Thread starter mjack003
  • Start date Start date
M

mjack003

Hi,

I've been trying to solve this problem for quite some time now but I
can't get a straight answer. I need to create a macro which will match
numbers from two lists and highlight the numbers on the master list
when the macro is ran. It should look work something like this:

conditional format:
formula is=CountIf($A$5:$A$4500 'from all grouped sheets',A5) =1

I don't know how I would program this into a VBA macro. The main idea
is A5:A4500 from all grouped sheets is compared to a5:a4500 on the
master sheet. Also, not all cells would be filled so I would need to
omit empty cells. Thanks in advance.

Regards,
Mjack
 
hello?

Is this even possible? Any help at all would be great because I'm
completely lost on this one....
 
I'm not sure I'd use conditional formatting that looks at grouped sheets. You
could have multiple windows open to the same workbook. And in some windows have
different grouped sheets.

But maybe you could adjust the color on demand with a macro:

Option Explicit
Sub testme01()

Dim mstrWks As Worksheet
Dim myCell As Range
Dim wks As Worksheet
Dim FoundIt As Boolean
Dim myAddress As String
Dim res As Variant

Set mstrWks = Worksheets("sheet1")

myAddress = "a1:a4500"

With mstrWks
For Each myCell In .Range(myAddress) _
.Cells.SpecialCells(xlCellTypeConstants)
FoundIt = False
For Each wks In ActiveWindow.SelectedSheets
If wks.Name = mstrWks.Name Then
'do nothing
Else
res = Application.Match(myCell.Value, _
wks.Range(myAddress), 0)
If IsError(res) Then
'keep searching
Else
FoundIt = True
Exit For
End If
End If
Next wks
If FoundIt = True Then
myCell.Interior.ColorIndex = 3
Else
myCell.Interior.ColorIndex = xlNone
End If
Next myCell
End With
End Sub
 
Where have you been hiding for the last couple weeks Dave? :) Once agai
you've saved me a lot of headaches and postponed the death of m
computer by being thrown out of the window. Thanks again.

Best Regards,
Mjac
 
Glad your computer survived.

PCs are a lot like boats.

The happiest two days in a PC owners life: The day they get one and the day
they sell it!
 
Back
Top