Case Code For changing Cell Color

Hi,

I am trying to create a Bar bending Schedule for construction sites. I am using VBA to help restrict and guide the users in what they are doing.

I have attached the spreadsheet I am working on to help.

I have compiled a Case statement which I want it to change the colour of particular cells based on the value entered into another cell as long as the cell has not data imputed

E.g.

If I enter 21 into cell I12 then Cell L12 will turn to index colour 41 if the cell is empty, the reason for this is that the blue cell is indicating it’s the last cell for a value to be entered the remaining cells are to remain blank. Now once I enter I value in cell L12 I’d want what ever text that is place to be place in ( ) and for the cell to return to index colour 2. (On the spread sheet I have manually indicated what I want)

the code I am using to change the colour of the cells is:

Please Login or Register  to view this content.

Can anyone help me with this please?

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Try this workbook.

1/. I have added a sheet with your shape codes listed, and added a dynamic named range "Shape_Codes" based on this list.
The list is numeric but formatted as Custom > "#,#00" this allows the dropdown list I have added to sheet "Bar Schedule" to display as "00", "01", etc...

You can add or subtract from this list as you wish, but the list should not contain any blanks.

2/. This code is added to the sheet module for Sheet "Bar Schedule", not the workbook module as in your sample.

Please Login or Register  to view this content.

Note that the Target.value is numeric and not text as displayed.

This code can be improved on, but let's see if it does what you want first.

3/. I have removed your "Before Save" Code from the sample for testing purposes.
(I suspect that this code should also be in a sheet module.)


Hope this helps

You might want to consider this, it allows for multiple deletions and fill down in Range("I12:I42")

It might just be a little more useable

Please Login or Register  to view this content.

Hope this helps