Macro to Highlight Contingent cells

[tcfraz4]

Hi everyone, been working on a excel project for a bit now and cant figure out part of it. In the project we had to create an annuity table with with spin buttons to change interest rate, number of terms ect. Anyway, I'm stuck on a part where we're supposed to highlight contingent cells. For example if there are only 5 payment terms then the chart only shows 5 rows of values. However, the maximum number of terms on the spin button is 30 and when i click my command button to highlight a certain type of data (ex. Principal amount) it still highlights 30 cells rather than just the 5 cells with values in them.

Im guessing i have to edit the macro assigned to the command button in microsoft visual basic but am not sure. Anyway this is what the macro looks like in visual basic.

Sub Highlight_Principal()
'
' Highlight_Principal Macro
'

'
Range("E9:E42").Select
With Selection.Interior
.Pattern = xlSolid
PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

Guessing it has something to do with the Range("E9:42").Select line but Im not sure how to edit it to highlight only the cells with values in them. Minimum number of values is 5 max is 30.

Any assistance would be greatly appreciated.

Thanks

 

[daverunt]

Hi,

See if this works for you. It stills selects the range but should only highlight those with content.

Sub Highlight_Principal()
'
' Highlight_Principal Macro
'

'
 Range("E9:E42").Select
 
 For Each Cell In Selection
 If Cell.Value <> "" Then
 
 With Cell.Interior
 .Pattern = xlSolid
 PatternColorIndex = xlAutomatic
 .Color = 65535
 .TintAndShade = 0
 .PatternTintAndShade = 0
 End With

 End If

Next
End Sub

 

[tcfraz4]

Thanks for the reply! That did not seem to change anything, still highlights everything in that column of the table. I assumed that the code would need an If or an If then statement but its been a few years since i took any type of programming class.
Feel like i still need to change this line but Im honestly not sure:
If Cell.Value <> "" Then

Thanks

 

[tcfraz4]

I think the issue might be that there are still formulas in the cells even if the cell is not showing a value.
ex. if values only go down to cell e23 then this is my formula in e24: =IF(A24=" "," ",SUM(C24-D24))

 

[daverunt]

Sorry been away for a while.

See if this does the job.

Sub Highlight_Principal()
'
' Highlight_Principal Macro
'

'

 Range("E9:E42").Select
 
 For Each cell In Selection

 If Not cell.HasFormula = True Then
 If cell.Value <> "" Then
 
 
 With cell.Interior
 .Pattern = xlSolid
 PatternColorIndex = xlAutomatic
 .Color = 65535
 .TintAndShade = 0
 .PatternTintAndShade = 0
 End With

 End If
 End If
 
Next
End Sub