Excel VBA conditional formatting based on next cell value

0
votes

I am trying to do some conditional formatting in Excel for the font size but seeing as it can't be done from the format menu, it needs to be done with VBA.

I have a range B6 to however many rows and I want to look at the cell next to it and see if it's blank (column C). If it is then format the cell to Bold and 11pt. If it's not blank then it needs to be normal and 9pt.

My code at the minute only makes the last row Bold and 11pt and the rest of the column, even if column C is empty will be normal 9pt.

What is going wrong? BTW I'm using Excel 2003

Dim c As Range, rng
Dim LASTROW As Long

LASTROW = Cells(Rows.Count, 1).End(xlUp).Row

Set rng = Range("B6:B" & LASTROW)
For Each c In rng
    If Len(c.Offset(1, 0)) = 0 Then
        c.Font.Bold = True
        c.Font.Size = 11
    Else
        c.Font.Bold = False
        c.Font.Size = 9
    End If
Next c
3
vbaexcel
You can do this with conditional formatting, using a formula – smirkingman
Using Excel 2003 so the option to change font size is greyed out – AdRock
It's not available in XL 2007/10 either. You do have to use VBA to change the font size. – Doug Glancy
@DougGlancy: Very true for font size :) – Siddharth Rout

3 Answers

3
votes

Your Offset parameters are backwards. You are checking the cell below the current one.

2
votes

Here's how

Note the trick is to use a single rule, coded for the top-left cell

0
votes

This doesn't need a macro - you can do it using a formula in Conditional Formatting.

Say you wanted to highlight the adjacent cell in column B red when the cell in column C had a value of "Red":

=IF(C6="Red",TRUE,FALSE)

then just fill down with the fill handle as usual.

Rule editor (2007):

enter image description here