How VBA Code Helps You To Automate Excel

If you've ever been knowledgeable with a table of headings and phone numbers it can be challenging sometimes to successfully see the facts you need. That's because the overall look and sensation of the worksheet can be boring and boring.

One simple technique is to use Excel's based upon design to make every second row a different colour; this leads to some noticeable evaluation, developing it much easier to research and perspective the facts. john oczypok

You can imitate this performance with your own VBA value and add some of your own upgrades to close your noticeable design on your information and systems.

VBA Concept To Identify Different Rows

While based upon design is an awesome system, you might want something a little different:

Highlight every 3rd row
Make every second row bold
Underline every 4th row
The technique here is to find every nth row and do something with the row. The platform for finding different sequence is this simple wide range of value, where n is the different row wide range you want to emphasize.

If rowNo/n=int(rowNo/n) then

' >>> go with found

End if
Because the int function earnings only the whole wide range it indicates that when the two aspects of the system are comparative then a go with has been discovered. In the following example, we'll try to framework a table starting in the first row.

To go with every third row the following VBA value would apply:

' select the place and set the different row numbers to emphasize.

Sheets(1).Activate

Range("a1").CurrentRegion.Select

n = 3
After selecting the existing place the value groups through each row and identifies a go with. Once a relevant row is discovered the cope with is saved to the different myRange and rather than emphasize the whole row, we'll just change the cells within the relevant row:

For x = 1 To Option.Rows.Count

If x / n = Int(x / n) Then

myRange=selection.Rows(x).Address
' design value goes in here

end if

next

Changing The Style Of A Related Row

How you want to framework the sequence is up to you but the best way to find VBA's design specifications is to record a macro modifying the framework of a cellular and modify it to do what you need.

'A in large background
Range(myrange).Interior.ColorIndex = 5

'Make every cellular in the row bold

Range(myrange).Font.Bold = true

'Create a wide range managing under every cellular in the row

Range(myRange).Borders(xlEdgeBottom).Weight = xlThin

Summary

This material has make an effort to show how simple VBA development can improve the use of one of Excel's main functions. john oczypok If you're posting systems and information consistently your own based upon design with VBA can only add to the stability of your execute.

Andy L Gibson is a former Web Web page developer rediscovering his interest in applications for organization.