Offset or something else?

Rave

New Member
Joined
Sep 11, 2006
Messages
38
Looking for some help here. I've managed to put together a macro for inventory at my business that works pretty well, but I've hit a bump in the road and am looking for some help. I currently have a spreadsheet where each row is a different item within my store. Each cell in that row represents a different bit of info on that item. For example, Cell B1 = a SKU number, Cell C1 = A Description, Cell D1 = Serial Number, Cell E1 = ID Number, etc. The way I have my macro set up, is it searches the entire spreadsheet for the requested item, very similar to the "Find" option built into Excel. I usually search by ID Number, but occationally have to go by Serial Number. I've got my macro set up so that when the item is found in the spreadsheet, it is automatically highlighted red. What I would like to do is to keep column A blank and in addition to highlighting the cell red, I would like to put an "*" in column A which corresponds to the the row of the found item. Example, I run my macro and search for ID Number 0025987. The macro finds the matching ID Number within the spreadsheet and highlights it red. Let's say it was found in Cell E17. I would like my macro to automactically put an "*" in A17. The offset property works if I search by the same column everytime. For example if I ALWAYS search by ID Number I can set the Offset Property, but if I search by Serial Number the Offset Property gets all messed up, because it puts my "*" in a column other than A.

Here is the code that colors the activecell for me.

With ActiveCell 'Set Color
.Interior.ColorIndex = 3 'Red=3
End With

How can I get the activecell to move to Column A within the current row?

Any help would be GREATLY appreciated!

Thanks,

-Rave
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi Rave
Welcome to the board

You don't need to move to write the asterisc. Please try

Cells(ActiveCell.Row, "A") = "*"

Hope this helps
PGC
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hello Rave,
You don't need to actually select an object (in this case your found cell) in order to work
with it. (But that's probably another story.)

Since you are, to answer your question directly you could add this line to your posted code.
Code:
With ActiveCell 'Set Color
  .Interior.ColorIndex = 3 'Red=3
End With
Cells(ActiveCell.Row, "A").Value = "*"
 

Forum statistics

Threads
1,136,612
Messages
5,676,796
Members
419,651
Latest member
alexanderguhr

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
Top