VBA select and looping issue with conditional formatting.

kyrgan

Active Member
Joined
Mar 23, 2006
Messages
309
Hello;
I am trying to run a VBA script since I can't use condtional formatting across sheets.

When I get to :
Code:
Sheets(CompareSheetName).Range(Cells(CompareLoop, 44)).Select

I get the very informative 1004 error.
Code:
Sub FindSamePLUs()

Dim MasterLoop As Integer
Dim CompareLoop As Integer
Dim MasterSheetName As String
Dim CompareSheetName As String

MasterSheetName = "Boise"
CompareSheetName = "Delta Waters"

For MasterLoop = 2 To 257 Step 1
    If Sheets(MasterSheetName).Cells(MasterLoop, 5) = 0 Then
    For CompareLoop = 2 To 257 Step 1
        If Sheets(MasterSheetName).Cells(MasterLoop, 5) = Sheets(CompareSheetName).Cells(CompareLoop, 5) Then
            Sheets(CompareSheetName).Range(Cells(CompareLoop, 44)).Select
            With Selection.Interior
                .ColorIndex = 3
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
            End With
        End If
    Next
Next

End Sub

I am not sure if this is the best method, any help is appreciated.

Thanks
K.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Hi You seem to have a "End If" missing between the two "Next" statements and the following line has a "Range" too many.
Code:
Sheets(CompareSheetName).Cells(CompareLoop, 44).Select
Hope that's of some help.
Regards Mick
 
Upvote 0
since I can't use condtional formatting across sheets

Yes you can...

Use Named Ranges.

on the OTHER sheet, highlight the range you want referred to, INsert - Name - Define
give it a name.

Now your conditional formatting can refer to that Named Range..

Hope this helps..
 
Upvote 0
Surprised no one answered that thread. Sorry..

I just put a reply there, see if it helps..
 
Upvote 0
Jon, thanks.
"Surprised no one answered that thread. Sorry.."

No need to be sorry. That is the nature of boards like this.

Your help is greatly appreciated.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
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