VBA solution

[Sakuji]

I enqired some time ago about a code for my spreadsheet and received great assistance. However, I am still a little confused about entering the code. What is the procedure once the spreadsheet that I wish to enter code for is open?

 

[R_Stephens]

Hi there,

To enter code into the Visual Basic Editor of Excel:

1) Open your Excel File that you want to put the code in.
2) Press Alt+F11 (to open the VBA Editor)
3) It the window to the right hand side of the page is grey, on your menu bar in the VBA Editor, click on Insert, then Module.
4) Enter your code in the text editor that then opens up in the right hand side of the screen.

Remember that the code you enter will need to be in a similar format to this

Sub <your Macro Name> ()

<your code>

End Sub

Hope this helps

Richard

 

[Sakuji]

Thanx Richard

Once I've entered the code do I need to activate it or run it as a macro in the spreadsheet or will it automatically take effect once I enter data into the sheet???

 

[R_Stephens]

Hi,

The information I have given above is to enter code that you have to run from the Tools>Macros controls.

If you are wanting the code to run when you do something specific, if you detail what you want to happen, I'll show you how to set it up.


Richard

 

[Sakuji]

I would like the cells in my worksheet to colour once certain data is entered for example if I enter "LTS" the cell turns green or "AL" the cell goes pink and "NDW" the cell goes red. There are about 20 different entries all with certain colours and I would prefer that the cells change as data is entered rather than running a macro once all data is entered. The example below should give you some idea.

NAME SUN MON TUE WED THU FRI SAT
Smith LTS LTS M SP SP SP RD
Jones NDW NDW
Taylor STS STS STS
Harris AL AL AL

For your information the abbreviations are
LTS =Long Term Sick
STS = Short Term Sick
AL = Rest Day Work
M = Medical
SP = Spare
RD =Rest Day

Many Thanks

Sakuji

 

[R_Stephens]

Hi,

In that case when you open the workbook. Got to the sheet where you want your code to run. Right click on the Tab for that sheet, and select 'View Code'

In the window that opens, above the text editing area are two drop down boxes. One on the left will say 'General', one on the right will say 'Declarations'. If you use the left drop down and choose Worksheet, the other one should default to Selection Change. Change the Right one to 'Change'

In the Text editing area the following should appear

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

You want to enter your code between these lines. Use Target in your code to determine the cell just changed.

For example to change the colour of the cell to Red if the cell value is LTS you would use, and Green if it is STS then

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.value="LTS" then
    Target.interior.colorindex=3
elseif Target.value="STS" then
    Target.interior.colorindex=4
end if

End Sub

Just put 'elseif Target.value=' lines in for each of your colour combinations

Hope this helps

Richard

 

[Sakuji]

Many Thanx Richard it works well, you've been a great help, saving me lots of time in my work. Thanx Again!