Need Macro to Format Cells based on corresponding cell values

[feckoffcup]

Hi,

I have a pretty large sheet (3000+ rows), whereby if the cell in column A of a row contains a number of between 1 and 150, then I would like the corresponding cell in Column E to be grey with a border around it. Obviously an arduous task if done using Copy & Paste and unfortunately I need this to happen in a number of sheets and don't fancy RSI at the end of the exercise.

As part of a second part of the project, I need to be able to 'Unlock' the cells that end up as grey w/border so that I can protect the sheet and only leave the grey cells as editable.

Hope someone can help!

TJ

[JOHN H. DAVIS]

For the first part maybe:

Please Login or Register  to view this content.

[jeffreybrown]

Using the code John alread started...

Please Login or Register  to view this content.

[feckoffcup]

Thanks amillion guys for looking at this, I am getting a syntax error on the following line:

Please Login or Register  to view this content.

I have changed "Sheet4" (which it just happened to be coincidentally!) to the name of my sheet "Business" but I am getting the same error.

[feckoffcup]

Sorry - just realised that the color kicked in within the CODE, the line did include the COLOR as you had included!

[jeffreybrown]

Are you sure the spelling is correct because the macro worked for me?

[feckoffcup]

I changed the name of the Worksheet to Sheet4 just in case, however I am still getting the same message - screenshot attached:

Capture.JPG

[feckoffcup]

I changed the name of the Worksheet to Sheet4 just in case, however I am still getting the same message - screenshot attached:

Attachment 193732

As you can see I attempted to change Sheets to Worksheets also to no avail - the error message is identical when Sheets is in place

[jeffreybrown]

Remove all that color stuff, it should just read...

Please Login or Register  to view this content.

Sorry about that I fixed post #3

[feckoffcup]

Thats at Jeff - worked a treat - thank you very much!

[jeffreybrown]

You are very welcome and glad you now have a working model. Thanks for the feedback.

============================================================================

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.