Excel-VBA Solutions

Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Pages

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)

Name

Email *

Message *