Conditional formatting across worksheets when looking for specific text

[frostii]

Hi again!
I'm hoping to have the following converted into VBA code if possible....

If
Sheet 2 column J (dynamic number of rows) contains the same text anywhere on Sheet 1 column J (dynamic number of rows)
Then
Fill Sheet 1 column A:G with light green color
Else
Fill Sheet 1 column A:G with red color.


For example, if the text "AID1003" is located anywhere in column J on sheet 2 and in column J on sheet 1, then color the row light green containing "AID1003" on sheet 1.


Does that make sense?

If so, thanks for any assistance!!!!!

[6StringJazzer]

This could be done with conditional formatting and no VBA. Is there a particular reason you want to use VBA?

[frostii]

Hi,

It's part of a longer macro - I'm doing a few cleanup macros first, then some formulas to pull data into Column J on both sheets, then hopefully the above conditional formatting.
Will put it all under a 'Click Here' button to do all the above in one go.

Thanks.

[frostii]

Sorry to bump, but I was hoping somebody could help???

Perhaps I need to make more sense? I'll try.....

I have column J with multiple rows (which will differ) on Sheet 1. This column contains various text cells (which will also differ).

I need to check this column against Sheet 2 column J. The column on Sheet 2 is all over the place, i.e. not in the same order as Sheet 1.

If there is a match (for example, Sheet 1 Column J Row 12 = "AID1003" matches Sheet 2 Column J Row 47 = "AID1003"), then I need to colour green Columns A:G on Sheet 1 (in this example, colour Row 12).

If there is no match, colour the row on Sheet 1 red.

Any ideas???

Thank you all in advance!

[6StringJazzer]

I still don't see the need to incorporate this to VBA. I would just create Sheet1 with conditional formatting in the appropriate rows and columns. It sounds like your formatting stays in the same cells all the time. You can do CF in VBA but it's messy and much easier to do it in the worksheet when you don't have to move it around dynamically.

If this must be in VBA then I would suggest using the macro recorder as a first step.