There are some threads on here where i think there was some Macro code to do something similar, if not i'm sure it has also appeared on SupportNet, however the search is rubbish and i couldn't find anything when i just checked.
This is from a chappie in the far east, Ian, i'm sure he won't mind me sharing it. I've not tested it and there are some caveats, plus i've edited as the copy and paste didn't layout quite right. Where it says SHADED it should just be shaded.
I'm sure you're supposed to be able to add the macro into the auto() sub so it works all the time but i never got that to work. @LosOjos may also be able to advise.
We donít have ticks but instead have shaded boxes. You could easily adjust it but I prefer the shaded boxes!
Support Required Slightly Below At Expected Slightly Above Above Handwriting 1 SHADED Spelling 3 SHADED Grammar 4 SHADED
In the second column is the data from SIMS, that teachers enter on their mark sheet. I set the formatting on the font so it is very small and white and I format the table so that it doesnít look like that column is there.
Then, in the Individual Report, you need to insert the macro that Iíve pasted below. What it does is:
1. Count how many tables there are and loop the code around each one in turn. For each table:
a. Count how many rows there are and loop around the rows in turn. For each row:
i. Look at the value in column 2 and take the first digit (just to clean it up a bit)
ii. Based on that value, shade in a specific cell.
It goes in here:
- Microsoft Word Objects
- This Document
∑ You must have Macros fully enabled.
∑ The colouring in script runs when word opens. This means that it colours in when you go to Preview, or Print. If you save then it will run when you double click on the document.
If you press upload, then it does not store it Ďcoloured iní. In theory, it should work when you open it. However, currently this is broken! I havenít looked at this yet, but if anyone has a solution please let me know!Code:Private Sub Document_Open() On Error Resume Next 'Count how many tables there are in the document intNumTables = Application.ActiveDocument.Tables.Count 'Set up a loop to repeat the code for each table in the document For intCurrTable = 1 To intNumTables 'Make all subsequent commands (e.g. .rows .cell) relate to the current table With Application.ActiveDocument.Tables(intCurrTable) 'Count How many Rows there are in the current table intNumRows = .Rows.Count 'Set up a loop to cycle around all the rows in the current table For intCurrRow = 1 To intNumRows 'Look at the cell value of the 2nd column strCellvalue = Left(.Cell(intCurrRow, 2), 1) Select Case strCellvalue ' Based on the number in that cell (1,2,3,4 or 5) colour in the correct cell Case "1" .Cell(intCurrRow, 3).Shading.BackgroundPatternColor = RGB(214, 227, 188) Case "2" .Cell(intCurrRow, 4).Shading.BackgroundPatternColor = RGB(214, 227, 188) Case "3" .Cell(intCurrRow, 5).Shading.BackgroundPatternColor = RGB(214, 227, 188) Case "4" .Cell(intCurrRow, 6).Shading.BackgroundPatternColor = RGB(214, 227, 188) Case "5" .Cell(intCurrRow, 7).Shading.BackgroundPatternColor = RGB(214, 227, 188) End Select Next intCurrRow End With Next intCurrTable End Sub