VBA Highlight Cell Based on Another Cell

AnnAnn

New Member
Joined
Mar 26, 2024
Messages
32
Office Version
  1. 2016
Hi Everyone,
I am trying to improve the run-time of this particular sub. Currently, it takes 94 secs to run on a 500 row file. I realize it is not going to be a "blink and it's done" due to the fact that it's looking at several columns on a row by row basis, but I'd like to see if there's something I haven't found during my Google and YouTube search (and this site). Below is the part of the code that I need help with, please.
I'm using the column names instead of the letters/numbers because this macro is on a workbook and runs on other open workbooks where the columns are not always in the same order.
The goal is that if the columns in the "If" line of code are populated and the columns in the checkColumns are not populated, highlight the blank cells in the checkColumns. Appreciate any help given.

VBA Code:
checkColumns = Array(colLtr4, colLtr5, colLtr6, colLtr7, colLtr8, colLtr9, colLtr10, colLtr11, colLtr12, colLtr13, colLtr14, colLtr15, colLtr16, colLtr17, colLtr18, colLtr19)

For i = 3 To lastRow

     If ws.Range(colLtr & i) <> "" Or ws.Range(colLtr1 & i) <> "" Or ws.Range(colLtr2 & i) <> "" Or ws.Range(colLtr3 & i) <> "" Then
         For Each col In checkColumns
                 Set rng = ws.Range(col & "3:" & col & lastRow)
                 Set headerCell = ws.Cells(2, col)
                 blank_cnt = 0

                 On Error Resume Next
                 blank_cnt = rng.SpecialCells(xlCellTypeBlanks).Count
                 On Error GoTo 0

                    If blank_cnt > 0 Then
                       rng.SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(255, 204, 0)
                       headerCell.Interior.Color = RGB(0, 0, 0)
                       headerCell.Font.Color = RGB(255, 255, 255)
                    End If

         Next col
     End If


Next i
VBA Code:
[CODE=vba]
[/CODE]
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Hi Everyone,
I am trying to improve the run-time of this particular sub. Currently, it takes 94 secs to run on a 500 row file. I realize it is not going to be a "blink and it's done" due to the fact that it's looking at several columns on a row by row basis, but I'd like to see if there's something I haven't found during my Google and YouTube search (and this site). Below is the part of the code that I need help with, please.
I'm using the column names instead of the letters/numbers because this macro is on a workbook and runs on other open workbooks where the columns are not always in the same order.
The goal is that if the columns in the "If" line of code are populated and the columns in the checkColumns are not populated, highlight the blank cells in the checkColumns. Appreciate any help given.

VBA Code:
checkColumns = Array(colLtr4, colLtr5, colLtr6, colLtr7, colLtr8, colLtr9, colLtr10, colLtr11, colLtr12, colLtr13, colLtr14, colLtr15, colLtr16, colLtr17, colLtr18, colLtr19)

For i = 3 To lastRow

     If ws.Range(colLtr & i) <> "" Or ws.Range(colLtr1 & i) <> "" Or ws.Range(colLtr2 & i) <> "" Or ws.Range(colLtr3 & i) <> "" Then
         For Each col In checkColumns
                 Set rng = ws.Range(col & "3:" & col & lastRow)
                 Set headerCell = ws.Cells(2, col)
                 blank_cnt = 0

                 On Error Resume Next
                 blank_cnt = rng.SpecialCells(xlCellTypeBlanks).Count
                 On Error GoTo 0

                    If blank_cnt > 0 Then
                       rng.SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(255, 204, 0)
                       headerCell.Interior.Color = RGB(0, 0, 0)
                       headerCell.Font.Color = RGB(255, 255, 255)
                    End If

         Next col
     End If


Next i
VBA Code:
[CODE=vba]
[/CODE]
Please disregard; I realized that the code is running efficiently.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top