www.OfficeInstructor.ca Join me Facebook.com/OfficeInstructor
Using Randbetween Function, Conditional Formatting and a Macro
2. Adjust Row Height for rows 2 to 22 as follows: Rows 2 &3 Height = 15.75, Rows 4:19 Height = 14.25, Rows 20:22 Height = 8
You can do that either by selecting the rows then dragging from the lower border in the row numbers area, or by going to the Format drop list on the Home Tab and select “Row Height”.
3. Select the range of cells that form the Christmas Tree. Because it is a non-adjacent range keep pressing CTRL while selecting. The range is: L2, K3:M3, J4:N5,I6:O7, H8:P9, G10:Q11, F12:R13, E14:F15, D16:T17, C18:U19
4. Name the range: Go to the Name Box (to the left side of the Formula Bar and Type a name: “Tree” then hit enter on your keyboard. Whenever you need to select that range you can click on the down arrow of the Name Box and select the assigned name.
5. Color the Tree: Select the named range Tree and change the Fill color to Green.
6. Color the Base: Select the range that forms the base of the tree K20:M22 and change the Fill color to Brown. You should have created the tree.
7. Generate Random numbers: Select the named range Tree and start typing a randomizing function, that generates random numbers between 1 & 10
=Randbetween(1,10) then hit
CTRL + Enter, to populate the function in the entire range. We’ll use these random numbers to apply conditional formatting then hide the numbers.
8. Conditional Formatting: Select the named range Tree one more time, then click on the down arrow for conditional formatting on the Home Tab and select Icon Sets, 3 Traffic Lights.
9. Hiding the Numbers: with the named range selected, on the Home Tab, select Conditional Formatting à Manage Rule from the drop menu. The “Conditional Formatting Rules Manager” dialog box opens. Click on Edit Rule…
10. The Edit Rule dialog box opens, check the box “Show Icon Only”. Then hit OK twice. Now you see the colored traffic lights icon without the numbers. Because the numbers are randomly generated by a Randbetween function, if these numbers change the conditional Formatting rule will reflect that change by showing different icons. You can intentionally chnge the numbers to test the functionality by hitting the F9 Key (Calculate now). With each hit on the F9 the color icons change giving the impression of a blinking light.
11. Now we want to automate the process of blinking so instead of triggering it manually with F9, we initiate the blinking by clicking on a picture of Santa. Download a picture you like from the internet and insert it to the left side of the tree in a suitable position and resize it by dragging from the sizing handles.
12. We’ll now write a little code in VBA and attach it to the picture of Santa. Press ALT + F11 to open the visual Basic Editor.
13. Click on the Insert Menu and select “Module”. A white blank area opens on the right side, with a title “Module 1”.
14. Copy and Paste the following lines in the “Module 1” window:
Dim Loopcounter As Integer
For Loopcounter = 1 To 15
Application.Wait Now + TimeValue(“00:00:01”)