Listing Using the Interior Object to Alter the Background of a Range

Sub InteriorExamp1e() Dim rg As Range

' create examples of each pattern Set rg = ThisWorkbook.Worksheets("Interior"). _ Range("ListStart").Offset(1, 0)

Do Until IsEmpty(rg)

' create examples of each VB defined color constant Set rg = ThisWorkbook.Worksheets("Interior"). _ Range("Co1orListStart").Offset(1, 0)

Do Until IsEmpty(rg)

rg.0ffset(0, 2).Interior.Co1or = rg.0ffset(0, 1).Va1ue Set rg = rg.0ffset(1, 0)

Loop rg.0ffset(0, 2).Interior.Pattern = rg.0ffset(0, 1).Va1ue rg.0ffset(0, 3).Interior.Pattern = rg.0ffset(0, 1).Va1ue rg.0ffset(0, 3).Interior.PatternCo1or = vbRed Set rg = rg.0ffset(1, 0)

Loop

Set rg = Nothing End Sub

This listing loops through two lists that I've created on a worksheet named "Interior". The first list, whose top left cell is named "ListStart", is a list of all of the xlPattern constants and their associated values. The second list, whose top left cell is named "ColorListStart", is a list of the VB-defined color constants and their associated values. This procedure loops through each of the lists. For each row, it applies the constant value in the second column to either the Pattern property or the Color property of the Interior object. The output of this procedure is shown in Figures 10.5 and 10.6.

Figure 10.5

An example of the various background patterns available in Excel

Figure 10.5

An example of the various background patterns available in Excel

Microsoft Office Ehcel

Figure 10.6

The basic colors are predefined.

Figure 10.6

The basic colors are predefined.

E3 Microsoft Office EHcel 2003 Beta - Chapter 10 EKamplesjds

BE®

ru

□le Eiíit Vrew Ireerr

Fermer locJ& s^atâ window

Help

. 3 X

Jjgj 10 - fi / g

mi iiii s=

. t-ja .on ' .on >.d

^JJiU^.A

* i

: _j

i »i *J j

* i à 2iiiij|

f> Pept^ wfcfi Changer:.. ; Fit^.m. , J

B

F3 - ft

A

. i

Ç

D

1

25

I ~|

26

VP Color CoiBlants

27

20

Nome

Value Exami

lie

ra;

29

ibBlsck

0 mM

30

vbBlue

ie7neaoH

at

vtiCjran

1677E9S0

32

vtiGieen

65230

33

vtiMagema

16711325 ■

34

vbRed

»5 H

35

vbWhile

16777315

30

vbYelltw

_

37

30

—ml

h <

( m\ aiwH. utiitk

III

- J

>J

| aeaJj

This section wouldn't be complete without looking at a way to display the colors occupying a workbook's color palette. Listing 10.4 loops through all of the colors on a palette and outputs each one's color index, color, and color value.

0 0

Post a comment