highlighting empty cells

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,805
Office Version
  1. 2016
Platform
  1. Windows
Hi
I want to highlight empty cells in table 1, I have 2 tables in the sheet and there are 3 columns separate them. So I clicked anywhere in table1 and then went to Home->Go special-->Blank.
It did highlight all the empty cells in table1 and all the 3 columns that separated these table and the empty cells in the second table as well. How can I ask GoSPecial-->Blank to only look for empty cells in Table1?
Thank you very much.

all.xlsx
ABCDEFGHIJKLMNO
1NameDeptGenderAgeSalaryStarting dateNameDeptGenderAgeSalaryStarting date
2John1HRM264518316/01/2018John1HRM264518343116
3Mary1ITF392478606/03/2015Mary1ITF392478642069
4Alex1SalesM638255909/02/2016Alex1SalesM638255942409
5Sara1MarketingF3030/09/2015Sara1MarketingF3042277
6Dan1AdminM322095715/10/2018Dan1AdminM322095743388
7Ali1SalesF194827719/09/2020Ali1SalesF194827744093
8Anna1ITF487877624/01/2020Anna1ITF487877643854
9Kim1HRF645294616/05/2019Kim1HRF645294643601
10David1HRM644667916/07/2019David1HRM644667943662
11Sandy1ITF366831909/01/2017Sandy1ITF366831942744
12Mike1SalesM384115431/08/2018Mike1SalesM384115443343
13Jack1MarketingM207121519/11/2017Jack1MarketingM207121543058
14John2AdminM746492601/09/2013John2AdminM746492641518
15Alex2ITM266769244188
16Alex2ITM266769223/12/2020Sara2HRF519103243294
17Sara2HRF519103213/07/2018Dan2HRM573788743201
18Dan2HRM573788711/04/2018Ali2ITF198023543716
19Ali2ITF198023508/09/2019Anna2SalesF454599943308
20Anna2SalesF454599927/07/2018Kim2MarketingF204323243507
21Kim2MarketingF204323211/02/2019David2AdminM481005243721
22David2AdminM481005213/09/2019Sandy2SalesF509990541629
23Sandy2SalesF509990521/12/2013Mike2IT42020
Sheet3 (2)
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Here is a VBA that may work for you

VBA Code:
Option Explicit

Sub Leza()
    Dim rng As Range
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Set rng = Range("A2:F" & lr)
    Dim c As Range
    For Each c In rng
        If c = "" Then c.Interior.ColorIndex = 3
    Next c
End Sub
 
Upvote 0
I think you can only do that by Conditional Formatting. Select all of the table (versus clicking in the table), then in CF use: =isblank(A1)
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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