Selecting all cells with a particular format

The example in this section demonstrates how to use the FindFormat method to locate and select all cells in a worksheet that contain a particular format. When these cells are selected, you can then do what you want with them —change the formatting, delete them, and so on. Figure 11-8 shows an example.

Hfind by formal.xls

HT

m

a

A I B

C

L

□ I

L

E

F

G

L

H j

J Til

1

262

305

60

442

174

120

85

58

361

81

2

58

m

¿fin

385

496

145

171

390

429

391

3

404

: Sel

ect Bold

251

272

354

9

439

299

189

4

470

_t Yell

ow Cells

249

153

222

114

456

390

20

5

173

Br

-TOT

-

418

338 260

276

352

138

113

6

138

253

326

75

111 274

436

259

109

348

7

343

230

189

256

Microsoft Excel

3

661 280

10 32

186

9

2319

1323

2050

2326

^■na

10

391

160

298

172

J

402

134

455

11

292

246

272

270

r

-,

430

296

222

144

480

400

453

OK

=j

493

317

31

13

53

203

441

359

_:

50

367

332

14

35

257

410

_

354

455

441

210

429

236

15

256

480

214

3l|

118

91

252

288

273

16

45

70

257

315

235

102

240

456

418

99

17

325

495

107

181

97

289 78

388

139

310

18

135

369

254

55

31

49

199

382

307

113

19

280

219

327

183

378

38 241

360

182

353

20

19 260

109

480

463

260

62

146

8

_

-

"22"

3091

2402

3410

254«

2484

3434

3023

_H

I" <

► »1 \ Sheet 1 / HI 1 » 11

Figure 11-8: Selecting all cells with a particular format.

Figure 11-8: Selecting all cells with a particular format.

The FindFormat property was introduced in Excel 2002.Consequently, this procedure will not work with earlier versions of Excel.

For reasons known only to Microsoft, recording a macro that uses the Format feature in the Find and Replace dialog box does not work. Therefore, you need to code these types of macros manually.

The SelectByFormat procedure is as follows:

Sub SelectByFormat()

' Selects cells based on their formatting

' Make sure version is Excel 2002 or later If Val(Application.Version) < 10 Then

MsgBox "This requires Excel 2002 or later." Exit Sub End If

Dim FirstCell As Range, FoundCell As Range Dim AllCells As Range

' Specify the formatting to look for With Application.FindFormat .Clear

.Interior.ColorIndex = 6 'yellow .Font.Bold = True End With

' Look for the first matching cell

Set FirstCell = ActiveSheet.UsedRange.Find(what:="", searchformat:=True)

' If nothing was found, then exit If FirstCell Is Nothing Then

MsgBox "No matching cells were found." Exit Sub End If

' Initialize AllCells

Set AllCells = FirstCell Set FoundCell = FirstCell

' Loop until the FirstCell is found again Do

Set FoundCell = ActiveSheet.UsedRange.Find _

(After:=FoundCell, what:="", searchformat:=True) If FoundCell Is Nothing Then Exit Do Set AllCells = Union(FoundCell, AllCells) If FoundCell.Address = FirstCell.Address Then Exit Do

Loop

' Select the found cells and inform the user AllCells.Select

MsgBox " Matching cells found: " & AllCells.Count End Sub

The procedure starts by setting properties of the FindFormat object. In this example, the formatting to be searched for consists of two components: a yellow interior and bold text. You can, of course, change this to any formatting that you like.

The Find method is used to locate the first qualifying cell. The Find method's What argument is set to an empty string because the search involves only formatting — not cell contents. Also, the SearchFormat argument is True because we are, in fact, searching for formatting.

If the formatting is not found, the user is informed, and the code ends. Otherwise, the found cell is assigned to the AllCells object variable (which stores all the found cells). A loop uses the Find method to continue searching but after the most recently found cell. The search continues until the cell found first is found again. Finally, all the found cells are selected in the worksheet, and the user is given the count.

This procedure will not locate cells that have a particular formatting as a result of Excel's conditional formatting feature.

0 0

Post a comment