validating cells in a range 2

[mudstuffin]

This is probably an easy one, but it's bugging me....!

In Excel, I want a sheet to validate the contents of all cells in a range, and say if the numeric value is over 50, I want that cell to go bold. How can this be done..? I can do this from an onentry sub, but want it to do it on worksheet activate, and which would seem to be a different approach to the 'if activecell' way.

Thanks in anticipation.


Greg

 

[LSTAN]

Hi mudstuffin,

You might want to try the below code, it's works for me.


Sub SelectBold_120()
' Makes each cell in the selection bold if it's greater than 120

Dim FormulaCells As Range
Dim ConstantCells As Range


On Error Resume Next

Application.ScreenUpdating = False

Set FormulaCells = Selection.SpecialCells(xlFormulas, xlNumbers)
Set ConstantCells = Selection.SpecialCells(xlConstants, xlNumbers)

For Each Cell In FormulaCells
If Cell.Value > 120 Then Cell.Font.Bold = True
Next Cell

For Each Cell In ConstantCells
If Cell.Value > 120 Then Cell.Font.Bold = True
Next Cell

End Sub

Hope this helps.

Regards

LSTAN

 

[mudstuffin]

Thanks LSTAN, for the reply.

This seems to work when I type a number in to a cell, but doesnt seem to update when a sheet activates. What I am trying to do is, is as soon as a sheet1 is opened, any values in a specific range (DataRange) should be validated and if above 50, turns bold at that point and without requiring any input, so that when the user first looks at the spreadsheet,the user can see what needs dealing with as a priority. Can you help on this....?

Thanks again



Greg.

 

[LSTAN]

Hi mudstuffin,

Try this,

Private Sub Workbook_Activate()
Dim myCells As Range

On Error Resume Next

Application.ScreenUpdating = False

Set myCells = Sheets("sheet1&quot.Range("dataRange&quot

For Each Cell In myCells
If Cell.Value > 120 Then Cell.Font.Bold = True
Next Cell

End Sub

Hope this is what you are looking for.

regards
LSTAN

 

[mudstuffin]

Thanks again LSTAN.

It works perfect.

Many Thanks.


Greg.

 

[euskadi]

Mudstuffin,

Conditional formating is built to do exactly this. If you apply the conditional format to the range you want to evaluate, you can achieve your result without resulting to VBA.

Try it, you'll like it

 

[mudstuffin]

Thanks for that EUSKADI.

I have had a look at conditional formatting, and it is very handy to just format the cells for the chosen criteria, but I wanted to figure out to do it through VBA for two reasons. Firstly, because I'm trying to learn VBA !, and secondly, I will probably want to adapt the procedure soon to maybe trigger a sub, and I'm not sure if that can be done through that. Or can I...?

That leads to me to another question....

Is it possible to trigger a sub from a formula, say an '=if' type..? I think I saw an example somewhere, but that didnt seem to work...

Thanks again.