Highlight Row of Selected Cell using VBA

Highlight Row of Selected Cell using VBA

While we are working in a big table of data and comparing with some other data or source, if the selected cell’s row gets highlighted then this can definitely help us in comparing and getting a sense.

Suppose our data is in the range B4:I14. Outside our range somewhere like the cell E17, let us name it as selRow. This is for keeping the details that which row to highlight.

Right click on the sheet name and choose the option of view code. Write the below code there.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    [selRow] = Target.Row

   

End Sub

The next step is to add conditional formatting to highlight selected cell’s row. For this select the entire range (B4:I14) and go to conditional formatting and new rule. Here select the rule type as Use a Formula and use the below rule and use your color formatting.

=ROW(B4) = selRow

It’s done now when you click in a cell of the range the whole row will get highlighted. Please experiment it and let me know in case of any issue.

Stop Duplicate Data Entry through Data Validation

Stop Duplicate Data Entry through Data Validation

As we know Data Validation tool can be used to create a drop down list in a cell and limit the values that user can enter.

But along this we can use Data Validation to stop users from entering duplicate data. Below are the steps for doing so.

Step 1: Select the range i.e. A1:A100 where we want only unique records’ entry and go to Data Validation (Data Tab > Data Validation).

Now select the validation type as Custom so we could use a formula. Now go to the formula field and type the below mentioned Countif formula.

=COUNTIF($A$1:$A$100,A1)<=1

Along this we can also setup the Error Alert for a custom message whenever a duplicate record is typed.

Once it’s setup, it will show like the below.

Extract Data by Advanced Filter & VBA

Extract Data by Advanced Filter & VBA

By Using Advanced Filter & VBA, we can filter our data for multiple options in a separate sheet.

Step1: Have you Raw Data (like given below) in a sheet namely RawData and create a table of it by pressing Ctrl L after selecting the data. The default table name would be Table1.

Date	Item	Client	SalesPerson
Monday, November 04, 2013	Mobile	Jim Smith	Peter
Wednesday, November 13, 2013	LapTop	Nadeem	Alyson
Saturday, November 16, 2013	PC	Saeed	Peter
Monday, November 25, 2013	LCD	Jim Smith	Alyson
Tuesday, December 03, 2013	Table	Nadeem	Alyson
Saturday, December 07, 2013	Table	Saeed	Alyson
Wednesday, December 11, 2013	Mobile	Jim Smith	Peter
Sunday, December 15, 2013	LapTop	Nadeem	Alyson
Thursday, December 19, 2013	PC	Saeed	Peter
Monday, December 23, 2013	LCD	Jim Smith	Alyson
Friday, December 27, 2013	Table	Nadeem	Alyson

Step2: In another sheet like in Sheet3 at top have your criteria or options with drop down box.

Suppose against the Item Client in the cell you want in drop down to appear Jim Smith, Nadeem, Saeed and a blank option. For that have your options like in this case the names in a separate sheet. Then select the cell where you want the drop down, go to Data tab and click on Data Validation. On the settings tab in drop down of Allow: select List. In the source provide the range where options items are like if in sheet2’s cells A2:A3 have the client names and cell A4 is blank then go to that sheet and select the range A2:A4. This way, we will be able to show a blank option as well.

Macro to run advanced filter and extract data

First part of the code is to make sure that the current filtered data if any is cleared out before we run the macro again.

The second part of the code is for copying the criteria we select in drop down and paste (special transpose) in the RawData sheet. This is the criteria that our final part of code would be using to filter data.

Put a button and assign this macro to it. For this first save the below code, by going to Developer Tab and then clicking the Visual Basic. A new window will open now click on Insert > Module, paste the below code there save and close the window. Now for putting a button in your sheet (i.e. Sheet 3 for this example) go to Developer tab then Insert then Click on button (Form Control). Place it where you want.

Now right click on it and click on Assign Macro and assign this Filterdata macro to that button. So now after you select your criteria, clicking on this button will run the macro.

One major benefit here is that you can run for all the three options after selecting them or load all data by selecting all options as blank or can extract data for any one or two items as well. Please try it and in case of any issue, please contact me.

Sub FilterData()

    Sheets("Sheet3").Select

    Range("B10").Select

    Range(Selection, Selection.End(xlToRight)).Select

    Range(Selection, Selection.End(xlDown)).Select

    Selection.Clear

   

     Sheets("Sheet3").Select

        Range("A2:B4").Select

    Selection.Copy

    Sheets("RawData").Select

    Range("M1").Select

    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

        False, Transpose:=True

    Sheets("Sheet3").Select   

   

    Sheets("RawData").Range("Table1[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _

        Sheets("RawData").Range("M1:O2"), CopyToRange:=Sheets("Sheet3").Range("B10"), Unique:=True

    Columns.AutoFit

    Range("B10").Select

     Sheets("Sheet3").Select

End Sub

Roman & Datedif function

Roman

The Roman function converts a number to Roman format.

=Roman(9) = IX

1	I
2	II
3	III
4	IV
5	V
6	VI
7	VII
8	VIII
9	IX
10	X

Datedif

This function calculates the difference between two dates in a variety of interims like number of years, months or days.

=DATEDIF(Start Date, End Date, Interim)

Here the Start Date must be less than the End Date and interim could be m for months, d for days and y for years.

And the if we are using the interim text directly in the formula then it should be in double quotes

VB Code to highlight modified cells

VB Code to highlight modified cells

So In an Excel sheet, you want to highlight if any cell is modified i.e. value changed or deleted then you can do this through the below VB Code.

How to do this?

Open a Workbook

Right click at the bottom of the sheet where sheet name is i.e. Sheet 1 and then Click on View Code.

Paste the below code in the window and save it.

Private Sub WorkSheet_Change(ByVal Target as Range)
Target.Interior.ColorIndex = 6
End Sub

Now test it by changing a value in any cell of that sheet and that particular cell will get highlighted.

Limitations: You can’t know if a cell was modified multiple times and color highlight can also be removed.

Using Index Function & Scroll Bar Button to Make a Dynamic Appointment Box

Using Index Function and Scroll Bar Button to Make a Dynamic Appointment Box

By using the Scroll Bar button and the Index formula, we can prepare the below workaround. The moment we click on the Scroll Bar button the date will change to next and so is the corresponding data like Place and Appointment details. How all this done, is explained here in this post.

The raw data we have is in the below table. For this data create a table by selecting the data and pressing the Ctrl L button. Click somewhere in the data of the table and you will see the Design tab under Table Tools on the main ribbon. Now in the Table name box by typing the table name you can change it to any like in this case it’s Events or you can go with the default name which would be like Table1 or so.

Serial No.	Date	Place	Appointment Details
1	10/1/2013	Islamabad	Meeting with Directors
2	10/2/2013	Peshawar	Site Visit
3	10/3/2013	Gujranwala	Farms Visit
4	10/4/2013	Faisalabad	Fish Farm Visit
5	10/5/2013	Lahore	Attending a Marriage
6	10/6/2013	Sheikupura	Meeting with Factory Managers
7	10/7/2013	Nawabshah	Farms Visit
8	10/8/2013	Hyderabad	Plant Visit
9	10/9/2013	Karachi	Meeting with Directors
10	10/10/2013	Gawadar	Site Visit

Now somewhere in the sheet put the first record number which is 1 and by using the Index function pull the information corresponding to that number which for now is 1 and the info we want to pull is first date, place and Appointment details.

So in the F1 cell we have written 1. So to pull the first date record, our formula would be below.

=INDEX(Events[Date],$F$1)

Here the Events table’s Date column is our array and we want to pull the first record so referring F1 cell where 1 is mentioned.

The result would be like 41548 (date in numbers), so to convert it to date format, use the below formula.

=TEXT(G2,"dddd d, mmmm")

Now similarly pull the Place and Appointment details by same way with Index formula.

=INDEX(Events[Place],F1)

=INDEX(Events[Appointment Details],F1)

Now our results would be like below.

1
Date	41548	Tuesday 1, October
Place		Islamabad
Appointment Details		Meeting with Directors

Go to next sheet or where you want to make that workaround/presentation box.

First Insert the scroll Bar button. For that go to Developer Tab the Insert and press on the Scroll Bar button.

By dragging you can place it anywhere and manage its size as well. Now right click on the button and open the Format Controls and Control tab. As out data range is from 1 to 10 so set the minimum & maximum values to 1 and 10 and incremental change to 1 so on a click the change is of one.  In the Cell Link type the cell reference or first click the cell link box and the cell which you want to refer. This would be the same F1 cell where we put 1 and used it in the Index function to pull the first record. So in this way a click on the button will change the value in F1 cell and accordingly our results of the Index formulas as well.  Now under the button in the cell provide a cell reference of the cell where we have a formula and are pulling the date and then in the next cell the Place and then the Appointment details. Fill colors in there and do some formatting to make it colorful.  Now pressing a button will populate the next date and corresponding details of Place and Appointment.