SERVING THE QUANTITATIVE FINANCE COMMUNITY

 
User avatar
mit
Topic Author
Posts: 1169
Joined: February 5th, 2005, 4:52 pm

VBA highlight all hardcoded cells

August 18th, 2015, 4:35 pm

I just want to find all hardcoded numeric cells and change their font colors, but my code below include both Texts and hardcoded numbers. Why the IsNumeric(rng) = true fails the exclude Text cells? ThxSub Hardcoded()Dim area As RangeDim rng As RangeSet area = Range("A1:M100")For Each rng In area If IsEmpty(rng) = False And IsNumeric(rng) = True Then rng.SpecialCells(xlCellTypeConstants).Interior.ColorIndex = 3 End If Next rngEnd Sub
Last edited by mit on August 17th, 2015, 10:00 pm, edited 1 time in total.
 
User avatar
ashkar
Posts: 274
Joined: October 17th, 2011, 9:25 am

VBA highlight all hardcoded cells

August 19th, 2015, 7:48 am

You could check the variant type explicitly. Something like below
 
User avatar
MHill
Posts: 488
Joined: February 26th, 2010, 11:32 pm

VBA highlight all hardcoded cells

August 19th, 2015, 10:44 am

Try:Sub Hardcoded() Range("A1:M100").SpecialCells(xlCellTypeConstants, 1).Interior.ColorIndex = 3End Sub
 
User avatar
MHill
Posts: 488
Joined: February 26th, 2010, 11:32 pm

VBA highlight all hardcoded cells

August 19th, 2015, 10:45 am

The "SpecialCells(xlCellTypeConstants, 1)" should limit it to only constants that are numbers & formatted as numbers
 
User avatar
tagoma
Posts: 18363
Joined: February 21st, 2010, 12:58 pm

VBA highlight all hardcoded cells

August 23rd, 2015, 10:44 am

Can't you just use a trick like this one?Assuming you have updated Excel at least once in the last 15 years, step 7 takes place as follows in the menu home tab -> conditional formatting -> new rule ... :
Last edited by tagoma on August 22nd, 2015, 10:00 pm, edited 1 time in total.
ABOUT WILMOTT

PW by JB

Wilmott.com has been "Serving the Quantitative Finance Community" since 2001. Continued...


Twitter LinkedIn Instagram

JOBS BOARD

JOBS BOARD

Looking for a quant job, risk, algo trading,...? Browse jobs here...


GZIP: On