Formatting Appearance Dynamically Without Selecting

[Dal123]

Hi Guys, a helpful person advised me yesterday of selecting Objects, Methods etc. by highlighting and hitting F1 to get help. The problem I find with this is that I can't understand what's going on in the help files. The help files don't show exactly how to write it as my knowledge is limited at this stage, I only understand Objects and Methods and I don't even understand them properly.

I want to format dynamically; I currently have a cell selected and ideally I don't want to move from this cell at this stage in my macro as I know it's more efficient to format directly.
Could someone show me how to write this properly and hopefully point me to some decent tutorials or literature on the language of VBA as I find the Microsoft guide poor, obviously I'm pretty thick and Microsoft's guide is probably perfectly written.

Please Login or Register  to view this content.

[AB33]

This line is wrong

Range (ActiveCell(-1, 6))

what are you trying to do?
What cell or range you want to apply to?

[Winon]

Hello Dal123,

Selected the Cell you want to format, then Start Record Macro, now format the Cell in which way you want, with Font Color and Style, Interior Color, Borders, to your hearts content. When done, stop the recording. Change the Cell back to what is used to look like, and the run your Macro. Walla, it does it all for you. You may do this on Ranges (More than One or Two Adjacent Cells.

There now, that should be a good learning start.

Regards.

[Dal123]

Thanks AB33; I'm trying to apply that formatting (in the correct syntax) dynamically to 1 cell above and for the six cells to the right.

Here is all of my code as it stands atm. I didn't include this previously as it can draw attention away from my current problem.

Please Login or Register  to view this content.

[Dal123]

Thanks Winon, the trouble with borders is that it bloats the code unnecessarily.

I'll certainly give it a good going over again though; many thanks.

[Winon]

Hello Dal123,

...the trouble with borders is that it bloats the code unnecessarily.

What I meant with my statement in my Post, is that you can format it how you like, with or without borders.

...now format the Cell in any which way you want, with Font Color and Style, Interior Color, Borders, to your hearts content.

[Dal123]

Thanks Winon, I got a bit confused when I saw all the code. I've only been on VBA a couple of weeks; though I'm very disappointed with the progress I've made.

The tutorials make it look easy and I think oh that's easy; then I go to do something and I fall apart lol. I've struggled badly with borders; then again I've struggled with everything so far lol.

Thanks again.

[AB33]

Most of the syntax you need can be founded by recording a macro. let me give some pointers:

Please Login or Register  to view this content.

ActiveCell.Offset(-1, 6)
can lead you to wrong result and syntax. The code is relying on the active cell. If the active cell is in row 1, you get an error as the code could not go above row1, so be careful with active cell.
You get a help in VBA by typing a key word and then press F1.
For e.g if you hover over your mouse on .Font.Bold and press F1, you get all the properties and methods for the object.

[Winon]

All is good Dal123,

If you could attach a sample workbook, I could demonstrate the recorded Macro for you and show you how to apply it to your Code in the right place or preferred Cells.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Dal123]

Thanks AB33, unfortunately I get an error around Automatic 'Sub or Function not defined'.

I have looked at the help files for a solid day near enough. I haven't yet grasped the syntax; I think the Microsoft Guide is quite poor and find external sources much better.

[Dal123]

Thanks Winon, the trouble is all the variables are fed from the user input at the start so I would need to upload the whole thing and I don't think it would be fair for you to sacrifice your spare time helping me.

Just thought I'd pop the question in case there was anything obvious I was doing wrong. I get that offsetting can be dangerous but these rows will never be at the top, could be causing me syntax issues though so that's a great point.

Many thanks

[AB33]

You need to run my code on its own.
I know your frustration with VBA helpline, I have been on you shoes. I think it is the way you search for help makes all the difference

Please Login or Register  to view this content.

[AB33]

Look for instance the help on borders.

https://msdn.microsoft.com/en-us/lib.../ff197210.aspx

[Winon]

unfortunately I get an error around Automatic 'Sub or Function not defined'.

It might have to do with the different versions of Excel between you and AB33.

[AB33]

'expression.BorderAround(LineStyle, Weight, ColorIndex, Color, ThemeColor)

This above is the syntax for borderaround. I made one of my own

BorderAround 1, xlThin, xlColorIndexAutomatic, 3, 6

[AB33]

I am going to doze-off uno momento. I will leave you with the good hands of Winon.

[Dal123]

Thanks AB, how does one format the cells relatively - from -1 above to the selection 5 cells to the right?

That formatting is how I want it; many thanks for that.

Sorry, we posted same time; many thanks guys. I'm also getting sleepy as it's quite late.

Thanks again guys; massively appreciated.

[AB33]

Selecting a cell has the same effect as putting the cursor on that cell.

With Selection.Offset(-1, 5)

Please note you have to select the cell before you run the code which in effect is the same as your cursor is on the cell before you run the code.
Winon has picked the error you are getting. Some of the properties and methods do not work in 2003. I am using 2010.

Please Login or Register  to view this content.

Both codes are the same, but using with and with end avoids repeating the same object on each line.

[Dal123]

Thanks AB33 and Winon, please don't think I've ignored this. I've been reading up on ranges all morning and working on this.

Still haven't grasped the limitations of each method of selecting.

[AB33]

There can only be one activecell; that is, where the cursor is, but could select multiple cells at a time.
The Range object has properties and methods, like any object.
Select is one of the property of a range, while offset is a method. Since offset is a method, you need to supply parameters.
Offset(row, column), where row and column are the parameters.
You do not need to do anything with property-just use the dot operator to use it. Range("A1").select.

[Winon]

Hi Dal123,

Please can you confirm what Formatting you have in mind for Cells without selecting each one, and I could maybe put a sample Workbook together for you?

[Dal123]

Thanks guys, Winon that's very kind of you but I've already taken up too much of your time and this has turned into an absolute war between me and Excel and I must win, even if it takes me a year lol.

AB33, that is exactly what I want; a complete understanding of the syntax! I'm going to read the VBA guide again.

[Winon]

Hi Dal123,

Winon that's very kind of you but I've already taken up too much of your time and this has turned into an absolute war between me and Excel and I must win, even if it takes me a year lol.

I admire your attitude and tenacity in this matter, but you may need a break now and again during said year or so.LOL.

Attached is a sample Workbook in xls format for you and AB33 to play with in the meantime!

Enjoy.

[Dal123]

Thanks Winon, greatly appreciated.

Been on Microsoft's site all day and getting a little grasp but I do find it lacking clarity and the navigation makes me cry .

[AB33]

You will get mental mate if you try to navigate the MS helpline. Here is a simple and easy trick to follow:
While on VB Project, hit the F2. This will take you to browse. Okay, the browse might not be helpful, but a good start to see all properties and methods for an object. I think the green boxes are for methods and the other box like donates a property.
If you properly reference a sheet and range name, you get auto completion, or IntelliSense. While your cursor is inside a code, just hit CRTL plus space. This will open us all the properties and methods for objects. Let's say in Winon's code. Type Rng then hit CRTL plus space, a pop up menu with all the properties and methods for the rng object will appear. All you have to do is select from the pop up drop down menu. That is Bingo!

[Dal123]

Thanks AB33, yeah the object browser is pretty cool, missing help for the little yellow boxes though.

[AB33]

In Winon's code, type anywhere inside the code. Type Rng then dot and stop. You see a drop down menu. I might have mixed them up, but one of them has green box.

[AB33]

Yes, I was right, the green boxes are methods.
Do this again.

Rng dote and then choose "Offset" from drop down and then a hit space, another menu appear which shows the arguments you need for the offset function as I had mentioned in one of my replies.

[Dal123]

Sorry to milk this one guys, I'm after formatting the range of cells from the code below. This formats the single cell offset at the specified parameter.

Please Login or Register  to view this content.

I struggle to see where VBA gives a clue for things like this "ColorIndex:=3". Nothing in Intellisence hints at this.

I would think that this would work

Please Login or Register  to view this content.

[AB33]

Dal,
You will get completely frustrated and probably give up on VBA by the steeps you are accelerating. You have to take a bit easy. I suspect you spent the entire day trying to get VBA stick on your head.
I was on your position when I joined the forum. I was tearing my hair apart on some concepts of VBA. The hardest bit I never forget is "Resize" method.

For e.g. this line is wrong. How do I know? As soon as I put it on module, VBA says foul with red colour. This is a syntax error. You can not run a code if there is a red line. Before you get upset, think how do I correct the error.
Remember all this line is trying to do is select a range and then you apply all the rest of the lines to the range.

Range(TableColumn + TopRow ":" TableColumn.End(xlToRight).Font.Bold = True

You try this line
Range (TableColumn + TopRow & ":" & TableColumn.End(xlToRight))
Now, I do not get a red line. Okay, the next step is when I run the code, I will get a run time error, why?
VBA does not know the values of all these unknown variables. You then look at TableColumn and TopRow and see what values are stored. You do it step by step

ActiveCell.Offset(-1, 6).BorderAround Weight:=xlThin, ColorIndex:=3

In VBA, and other languages, you can pass arguments by either name or position. In fact, VBA is a spoon-fed language. All the methods and properties for all objects are given to you, unlike other languages in which you need to create your own objects.
This one, we are passing the arguments by name: ColorIndex:= is the argument name and value is 3. VBA gives you the argument, but it can not choose it for you. In VBA, there are two types of arguments: Must and Optional. If the argument is in square bracket, it is an optional argument, you can leave it if you wish.

Please Login or Register  to view this content.

[Dal123]

Thanks AB33, I've taken your comments on board and trying to slow down a bit. Yes I've been on the computer non-stop for the past two weeks lol.
So I've stepped away from my larger project and setting up test sheets individually on each command.

In an effort to try to help someone else one day this is what I've encountered using 2010:

It seems that one cannot use the Range command with two varaibles for column and row when trying to select or format a block. So my best option is to format by using ActiveCell and Offsetting

Please Login or Register  to view this content.

It also seems that I cannot format a block of cells with a nested set of parameters (not sure if correct lingo here) so one must address the Active.Cell and apply formatting to each property/ attribute individually like so (which seems unneccessarily bloated:

Please Login or Register  to view this content.

Unfortunately the below gives me syntax errors on the first line.

Please Login or Register  to view this content.

[AB33]

These two codes are the same, but I will avoid using with and end with statement as it confused me lots when I was learning VBA.

Please Login or Register  to view this content.

A range address is a string. Range("A1") means the activecell if the cursor is on A1. Offset is a relative, not a fixed position.
Range("A1").offset(0,1) means I am now in Range("B1"). I moved 0 or stayed on the same position (I have not moved vertically- Down or Up). 1, means I am moving one to the right, but this only applies to A1. Range("A1").offset(-1,1) will give me an error ,as I can not move one more up than A1. All these positions are relative, i.e. to where you start. So, if you are using active cell to locate the address, the offset moves with the active cell; That is, where ever your cursor happens to be at a time when you run the code.

[Winon]

Hi Dal123,

I struggle to see where VBA gives a clue for things like this "ColorIndex:=3". Nothing in Intellisence hints at this.

Like AB33 said, VBA is a spoon-fed scenario, and you will find that the ColorIndex No. does not always return the exact Color you desire, whereas RGB does!

In the attached sample Workbook you will find a lot of duplicated ColorIndex Numbers (highlighted in Red) which is not the exact same Colour. Using RGB instead returns the exact Colour you want.