Need to bold cells where the formula result is greater than or equal to 10 via VBA

Question

I need to apply bold to all cells within a certain range where the formula result is 10 or more. I've tried the following code but it seems to apply bold randomly!

Sub BoldHighHours()

Application.ScreenUpdating = False
Dim c As Object

For Each c In Range("I7:AM1005")
   If c.Value >= 10 Then
       c.Offset(0, 1).Font.Bold = True
       c.Offset(0, 2).Font.Bold = True
   Else
       c.Offset(0, 1).Font.Bold = False
       c.Offset(0, 2).Font.Bold = False
   End If
Next
Application.ScreenUpdating = True
End Sub

If you've been following my previous questions/saga you'll understand why I can't use conditional formatting! Autofilter doth not look kindly upon large amounts of conditional formatting and punishes ye with slowdown greatly!

generally, code looks fine. Do you understand what `.Offset(x,y)` do? Do you use it intentionally? — Kazimierz Jawor, Apr 05 '13 at 09:35
So, to clarify your logic, you are looping through some 31000 cells and setting formats of the two cells next to it? Now, if J10.value is >10, then K10 and L10 are bold. next, it looks at K10 and if K10.value>10, then L10 and M10 are bold, next it looks at L10, and if L10.value>10, then M10 and N10 are bold ... and so on... wow...and that is quicker and more efficient than using Conditional Formatting? — Our Man in Bananas, Apr 05 '13 at 09:59
surely you could speed up your Autofilter by using Sort first? — Our Man in Bananas, Apr 05 '13 at 10:01
Are you sure you want to loop through all columns `I` to `AM`, and **then** bold the right 2 cells?this doesn't make sense to me... — glh, Apr 05 '13 at 10:24
Please tell us what you need to achieve because your code is certainly not doing what you think it is. — Our Man in Bananas, Apr 05 '13 at 10:28

glh · Accepted Answer · 2013-04-05T12:07:36.783

1

You need to remove the Offset():

Sub BoldHighHours()

    Application.ScreenUpdating = False 
    Dim c As Range 

    For Each c In Range("I7:AM1005") 

        If c.Value >= 10 Then 
            c.Font.Bold = True
        Else 
            c.Font.Bold = False 
        End If 

    Next 
    Application.ScreenUpdating = True 

End Sub

my optimisation:

Sub BoldHighHours()

    Application.ScreenUpdating = False 
    Dim c As Long

    For Each c = 9 to 39 ' hopefully i to am..

        ActiveSheet.AutoFilterMode = False

        With Range("A8:A1005").Offset(0, c - 1)

            .Font.Bold = False
            .AutoFilter Field:=1, Criteria1:=">=10"
            .Font.Bold = True
        End With 

    Next 

    ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True 

End Sub

edited Apr 05 '13 at 12:07

answered Apr 05 '13 at 10:30

glh

4,900
3
23
40

there must be a better option than 31000 operations...? – Our Man in Bananas Apr 05 '13 at 11:35
@Philip, 31k is nothing, check this 2.5 billion one out http://stackoverflow.com/questions/15830617/excel-vba-find-all – glh Apr 05 '13 at 11:39
another situation where ADO and SQL can make a big problem so much smaller! – Our Man in Bananas Apr 05 '13 at 11:58
+1 nice work! I am currently trying to reduce the 2.5 Billion one down by a factor of maybe 1000! – Our Man in Bananas Apr 05 '13 at 12:09
well, in fact if the OP can use a formula on the sheet, it can be only a few thousand, otherwise, using ADO and OLE DB is the way to go, reducing it to just a couple of operations using a SQL Query and *CopyFromRecordset* – Our Man in Bananas Apr 05 '13 at 12:31
2.5 billion? Holy guacamole! Anyway, sorry yes I cannibalised the code from elsewhere on the internet and didn't realise the offset function was COMPLETELY not needed. Your optimisation is amazing!! I'm slowly beginning to see the massive potential in using Range If and Else! Why the hell didn't I find this site lonnnng ago?? – Matteous Apr 05 '13 at 15:11
here are some other sites for you: [Chip Pearson Excel MVP](http://www.cpearson.com) and [Charley kyd Excel MVP](http://www.exceluser.com) and [Ron de Bruin Excel MVP Legend](http://www.rondebruin.nl) and [Colo MVP Excel Legend](http://www.mrexcel.com) and of course, who could forget [Contextures](http://www.contextures.com) or *the genius of all genius's* [All Experts - Mr Tom Ogilvy](http://www.allexperts.com/ep/1059-2697/Excel/Tom-Ogilvy.htm) – Our Man in Bananas Apr 05 '13 at 16:19
here are some other sites for you: [Mr McGimpsey & friends](http://www.mcgimpsey.com/excel/index.html) [Excel Author MVP Mr Ron Bovey](http://www.appspro.com/Tips/Tips_And_Tricks.htm) and [Mr Spreadsheet Himself - John Walkenbach](http://spreadsheetpage.com/index.php/tips) – Our Man in Bananas Apr 05 '13 at 16:32
Thank you for all your help :) I shall investigate the links you've provided – Matteous Apr 08 '13 at 01:11

Need to bold cells where the formula result is greater than or equal to 10 via VBA

1 Answers1