Trying to use a match function to format a cell in VBA

**mdoherty83** · 08-15-2012, 04:23 AM

Hi there,

as per previous posts I'm new to VBA and am trying teach myself as I go, so please, treat me like an idiot!

I've just tried writting the following;

Please Login or Register  to view this content.

This is for a drawing register/tracker spreadsheet. the idea is, when a new a new drawing revision is recieved the new revision (C2, C2A, C2B, C3, C3A, . . . . ) is inserted into "J" column. I'm trying make the marco look along the top row to find the matching revision, and then format the corresponding cell in origianl row, I.e, if it i entered a new revision number into Cell "J5, it would look it up in row 1, and if the matching revision was in "H1", it would format "H5".

I think my issue is (once again for anyone who say my last thread) a problem with getting used to VBA syntax as appose to normal worksheet syntax.

Thanks in advance,

**JBeaucaire** · 08-15-2012, 04:30 AM

There's no need to apologize for not knowing something, nor feel silly asking a question on a forum setup and designed for this very purpose. The dumbest questions are the ones you should ask and don't.

Anyway, a sample workbook might make it 100% clear what you're doing, but I imagine something like this if I understood your verbal correctly:

Please Login or Register  to view this content.

There are a myriad ways this could still be wrong, so hopefully this just gets the syntax corrected for you and you can tweak the targeting.

**mdoherty83** · 08-15-2012, 04:33 AM

Minor correction, this line hould read

Please Login or Register  to view this content.

Well, i say it should, it still doesn't work, but just thought I'd make that correction

---------- Post added at 03:33 AM ---------- Previous post was at 03:32 AM ----------

Hi Jerry, I'll give that a try now. Just one thing, what does the the "Option Explicit" at the begining mean?

**mdoherty83** · 08-15-2012, 04:47 AM

Thanks Jerry, Good news and bad

It did format a cell but. . seems to be formatting a cell 7/8 to right of where it should? I've attached the worksheet so hopefully you can see what I mean

Also, What does "Option Explicit" do?

**JBeaucaire** · 08-15-2012, 04:49 AM

"What does Options Explicit do?" sounds like a perfect Google question.

Your observations might have led you to an edit.... you're searching row1 for a match, but the match results in a colored cell 7 columns too far to the right? Maybe because the match values start in column 8? Heh,

So, just correct the MATCH:

Please Login or Register  to view this content.

Seems odd to do with VBA what conditional formatting can do for free.

**mdoherty83** · 08-15-2012, 05:03 AM

Got you now! Thanks Jerry.

Still getting used to the different commands in VBA, but at least thats this one clearer now. In answer to your question, about why not use conditional formatting? I want the this track which revisions we have recieved, so once a cell is formatted, it will stay formatted even when we update it and format another cell with a higher revision, and because we don't recieve every revision, i cannot use a "less then" type condition.

Thanks again, I will no doubt, be back with more!