How to Find RGB Value of Fill Color of Any Cell

If you get a excel sheet like this, do you know how to find the RGB value of fill colour of these cells?

This excel sheet has cells with 3 different fill colors. In this post I will explain how to find RGB value of fill color of any cell you want. Let’s try to find the fill color of cells in first row(Light blue color cells). First, select one of the light blue cells. I selected the B2 cell.

Then go to the “HOME” tab and click arrow head next to “Fill Color” icon.

Then click on “More Colors...”

“Colors” window will open. You can find the RGB value under the “Custom” tab.

So the RGB value of fill color of B1 cell is RGB(173, 201, 220). You can use this method to find RGB value of fill colors manually.

Calculate absolute value in VBA (Abs function)

In our last post we learned how to calculate absolute value using Excel worksheet function. Today let’s learn how to calculate absolute value in VBA.

Here is our sample data set. As you can see we have few companies and change of their stock values.

Positive values are in green. Negative values are shown in red. If there is no change then values are in black. Assume we need to calculate the absolute value of change of the each company. We can use following vba function for that.

Abs (Number)

Number should be any numeric expression. So if we want to find the absolute value of change of “General Electric Company” then we can find it by

Abs(Range("B2").Value)

As we need to find absolute value of several cell values we can use For loop as follows.

Sub CalculateAbsValue() Dim i As Integer For i = 2 To 8      Range("C" & i).Value = Abs(Range("B" & i).Value) Next i End Sub

After you run above macro you will get following result.

And this post explains how to use worksheet ABS function.
How to Use Excel ABS Function

How to Use Excel ABS Function

We often need to find absolute value of a number or absolute difference of two values. Fortunately we have inbuilt Excel function to calculate that. It is call “ABS” function. So in this post I will teach you how to use Excel ABS function.

Here is our sample data.

Now let’s find the the absolute value of value in B4 cell. You can find it using this formula.
=ABS(B4)

When you click enter, result will be shown like this.

Now take your cursor to bottom right corner of the C4 cell. Then cursor will changed to + mark. Click the left mouse button and drag until C8 cell. Function will added to all the relevant cells.

Now let’s learn how to find absolute difference of two values. Let’s consider following example.

First let’s find difference of C4 value and D4 value. We can use following formula for that.
=D4-C4

When you click enter, value will calculated like this.

Next fill all the cells in column E with formula like we did earlier.

As you can see, we have both positive and negative values. Assume we need to find absolute weight difference for two months. We can use ABS function for that.
=ABS(D4-C4)

When you click enter value will be calculated like this.

Next fill rest of the cells in the column F with formula.

Now we have absolute difference of weights of two months for each person in column F.

If you want to learn how to find absolute value in vba then read this post.
Calculate absolute value in VBA (Abs function)

Hide And Unhide Worksheets Using VBA

Sometimes we need to hide or unhide sheets in Excel. If you develop an advance application with lots of excel databases and VBA forms, then you may need to hide the database from the users. So then they can only alter data with userforms. In this post first I will explain you how to hide or unhide worksheets manually. Then I will teach you how to do it using VBA.

Assume we have an Excel workbook like this. So we have 3 sheets in our file.

If we want to hide one of the sheets manually we can do it as follows. First right click on the worksheet name you want to hide. Then click on “Hide”.

As I click on the “Sheet 1”, it will hide the “Sheet 1”

And if you need, you can hide several sheets at once. What you need to do is select all the sheet names you want to hide while holding down Ctrl key in your keyboard. (However you can’t select all the worksheets in the workbook to hide. Because there should be atleast one visible sheet in the workbook.) Then right click on one of the worksheet name you want hide and click on “Hide”.

This will hide all selected sheets.

Next let’s learn how to Unhide sheets manually. You can do it as follows. First right click on one of visible worksheets. Then click on Unhide.

Then it will show list of hidden sheets like this.

Select the worksheet you want to unhide and then click OK. Selected worksheet will become visible.

Now let's look at how to hide a worksheet using VBA. Assume you have worksheet call Sheet1. And you should have at least one more sheet in the workbook in addition to Sheet1. You can hide Sheet1 like this.

Sub HideSheet() Worksheets("Sheet1").Visible = False End Sub

And if you want to unhide that sheet using VBA then you can do it as follows.

Sub UnhideSheet() Worksheets("Sheet1").Visible = True End Sub

However if you use above method to hide sheets, then users have ability to unhide them manually if they want. But there is another method you can hide sheets which doesn't allow manual unhide. If you use that method then worksheets can be unhidden only using a above unhide code. Here is the code.

Sub VeryHidden() Worksheets("Sheet1").Visible = xlVeryHidden End Sub

So assume we have 3 sheets like this.

And we run above code.

As you can see the Sheet1 is now in hidden state. But you can’t unhide it manually as it doesn’t show hidden sheet.

So you need to use above Unhide code to make it visible.

How to Read, Write, Delete and Move excel comments

In this post let’s look at how to automate Excel cell comments using VBA. If you want to manually enter a cell comment, then you can do it as follows. First right-click inside a cell. Then click on “Insert Comment”.

Then Excel will create a box like this.

Now you can enter your comment.

So if you have a spreadsheet with lots of data, how do you identify the cells which have comments? It is easy. Cells with comments will have red color triangle shape in top right hand corner of the cell. In this example there is a comment in D3 cell.

If you want to read the comment you can take your cursor on top of that cell. Then it will show the comment.

Now let’s look at how to read this comment using VBA. If the comment is in cell D3 then you can read it as follows.

Sub ReadComment() Dim StrComment As String StrComment = Range("D3").Comment.Text Debug.Print StrComment End Sub

If you run above macro, at the beginning comment will be assigned to string variable call “StrComment”. Then it will be printed in immediate window.

Next let’s learn how to write a comment to a specific cell. Below is the code to write a comment to a cell. This will add comment “This is sample comment” to cell G5.

Sub WriteComment() Range("G5").AddComment "This is sample comment" End Sub

Also note that you can replace “Range” keyword with “Cells”. So instead of Range(“D3”) you can write Cells(3,4) and instead of Range(“G5”) you can write Cells(5,7)

So if you want to move comment from B2 cell to C2 cell then you can do it as follows.

Sub MoveComment() Dim StrComment As String StrComment = Cells(2, 2).Comment.Text Cells(2, 3).AddComment StrComment End Sub

Also if you are developing a dynamic application, it is important to check whether there is a comment in the cell before try to read the comment. Because otherwise excel will produce runtime error if program try to read a comment from a cell where there is no comment. You can check whether there is a comment in cell A1 using following code.

Sub CheckForComment() If Cells(1, 1).Comment Is Nothing Then      MsgBox "No comment" Else      MsgBox Cells(1, 1).Comment.Text End If End Sub

If there is no comment it will give you “No comment” message. If there is a comment then macro will show the comment in a message box.

Create new workbooks from each and every worksheet in your workbook | Excel VBA

In an earlier post I explained how to create a workbook from a particular sheet in Excel VBA. Today I will teach you how to create new workbooks from each and every worksheet of your workbook and how to save them to desired location. So here is the workbook I have.

Name of this workbook is “Original file.xlsm”. As you can see this workbook has three worksheets. So I’m going to create 3 separate workbooks from them and save them in folder where this “Original file.xlsm” is in. This is how the folder look like before run the macro. It has only one file.

So below is the full code to separate all sheet to different workbooks and save them.

Sub CreateWorkbooks() Dim WB As Workbook Dim WS As Worksheet For Each WS In Worksheets    WS.Copy    Set WB = ActiveWorkbook    WB.SaveAs ThisWorkbook.Path & "\" & WS.Name, FileFormat:=52    WB.Close Next WS End Sub

Here is the result after running above macro. As you can see three files have created with original sheet names.

Next I will explain about this code in detail. First we define our variables

Dim WB As Workbook Dim WS As Worksheet

Next we need to loop through all the sheets of our workbook.

For Each WS In Worksheets Next WS

As you can see there are few other lines between above two lines. So those commands will be executed for each and every worksheet. Following commands will create a new workbook from each sheet.

WS.Copy Set WB = ActiveWorkbook

Now let's take a closer look at the following line.

WB.SaveAs ThisWorkbook.Path & "\" & WS.Name, FileFormat:=52

What this line does is, it save each workbook in .xlsx format in the folder where we have our original file. ThisWorkbook.Path gives the location of the folder. And WS.Name set the name of the workbook. So here each file is saved with it’s original sheet name. And if you want to save the files in a different location you can replace the ThisWorkbook.Path with desired folder path. Here is an example.

WB.SaveAs "C:\Users\EVS\Documents\" & WS.Name, FileFormat:=52

Also FileFormat number specifies the format when saving the file. Here is the list of excel file formats.

XlFileFormat Enumeration (Excel)

And following line is used to close each newly created workbook.

WB.Close

How to Create a New Workbook from Existing Excel Sheet in Excel VBA

In this post I will share a quick tip with you. Sometimes we need to create a new workbook from existing worksheet in Excel VBA. This existing worksheet can be active worksheet or any other worksheet. Assume we have a workbook like this.

This workbook contains 3 sheets. Think we need to create a new workbook from “Sheet3”. We can do that using following code.

Sub CreateNewWorkbook() Dim WB As Workbook Dim WS As Worksheet Set WS = Worksheets("Sheet3") WS.Copy Set WB = ActiveWorkbook End Sub

If you run above macro you will notice a new workbook created from Sheet3 like this.

Now we can see two workbooks in taskbar. Our original workbook and the newly created one.

And if you want to create a new workbook from active sheet you can use below code.

Sub CreateNewWorkbookFromActiveSheet() Dim WB As Workbook Dim WS As Worksheet Set WS = ActiveSheet WS.Copy Set WB = ActiveWorkbook End Sub

If you want to learn how to create workbooks from each and every sheet of the workbook and save them to a desired folder, then check this post.

Create new workbooks from each and every worksheet in your workbook