Serving the Quantitative Finance Community
VBA highlight all hardcoded cells
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.
VBA highlight all hardcoded cells
You could check the variant type explicitly. Something like below
VBA highlight all hardcoded cells
Try:Sub Hardcoded() Range("A1:M100").SpecialCells(xlCellTypeConstants, 1).Interior.ColorIndex = 3End Sub
VBA highlight all hardcoded cells
The "SpecialCells(xlCellTypeConstants, 1)" should limit it to only constants that are numbers & formatted as numbers
VBA highlight all hardcoded cells
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 tags on August 22nd, 2015, 10:00 pm, edited 1 time in total.