Conditional formating macro: If one cell is blank then nest two bold and Filled

[tweek]

Hi.

Can someone help me with this one?

I have a long list of numbers in columns A to F

What I want to do is to make a macro that makes cell B and C turn bold and filled with some color if cell D in the same line is blank. And make this macro run through all my lines ( a few thousand).

Is this possible ?

Thank you!

 

[decklun]

Hi Tweek - welcome to the board!

If all of the cells in column A will for sure have data, then you can use code to find the last row, and then have your code loop thru each row looking for an empty cell in column D.

See if this will do what you want.....

Sub CheckForBlanks()

Dim wb As Workbook
Dim ws As Worksheet
Dim LastRow As Long

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1") 'change name if applicable
LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

For a = 2 To LastRow 'assuming row 1 is header
    If IsEmpty(ws.Cells(a, 4)) Then 'check column D for blank cell
        ws.Cells(a, 2).Font.Bold = True 'bold column B
        ws.Cells(a, 2).Interior.ColorIndex = 4 'color column B green
        ws.Cells(a, 3).Font.Bold = True 'bold column B
        ws.Cells(a, 3).Interior.ColorIndex = 5 'color column C blue
    End If
Next a
 
End Sub

 

[Norie]

Couldn't you just do this using standard conditional formatting?

Select the relevant rows in columns B & C, goto Format>conditional formatting..., select formula is and enter this changing the 1 to the row where the data is

=$D1=""

Now format as required.

 

[tweek]

Hi guys, Thank you VERY much!

Both methods work for me.

Decklun, your code is exactly what I was hoping for!

thank you again! :D