Excel formatting issues

[Derek27]

I'm using VBA to write bet instructions to the spreadsheet but it keeps changing the format, clears the green background, larger font and changes odds to currency!

Has anyone else had this problem or know what the cause is?

Capture.PNG

[greenmark]

I'm using VBA to write bet instructions to the spreadsheet but it keeps changing the format, clears the green background, larger font and changes odds to currency!

Has anyone else had this problem or know what the cause is?

Capture.PNG

Record macro.
Reformat the cells manually to your preference.
Save macro then examine it in VBA. Then you can see the required VBA syntax to reformat to include in your VBA instructions.
Or perhaps I've misunderstood. Which is quite likely.

[Derek27]

I'm using VBA to write bet instructions to the spreadsheet but it keeps changing the format, clears the green background, larger font and changes odds to currency!

Has anyone else had this problem or know what the cause is?

Capture.PNG

Record macro.
Reformat the cells manually to your preference.
Save macro then examine it in VBA. Then you can see the required VBA syntax to reformat to include in your VBA instructions.
Or perhaps I've misunderstood. Which is quite likely.

I know how to change cell formats in VBA but there wouldn't be much point if BA or something else is changing them. What I don't understand is that normally when you use Range("A1")=5 VBA simply places a 5 in cell A1 without changing font, backcolour or data type.

[foxwood]

I use this and format not affected - various versions of Excel, latest being 365

Code: Select all

' 'i' is the selection number from top to bottom of BA master sheet
Worksheets("Bet Angel").Range("L" & (9 + (2 * i))).Value = "LAY"
Worksheets("Bet Angel").Range("M" & (9 + (2 * i))).Value = LayOdds

EDIT based on your latest comment think it could be you are not using ".Value" ?

[Derek27]

Thanks greenmark and foxwood (I was going to say greenwood ) but I've just solved it.

I was using Range.Clear. Should have been Range.ClearContents.

[Derek27]

I won't start a new thread but I notice the BA spreadsheet templates are in Excel 97-2003 format. Can you save them in an up-to-date format? Does it matter?

[foxwood]

I won't start a new thread but I notice the BA spreadsheet templates are in Excel 97-2003 format. Can you save them in an up-to-date format? Does it matter?

I'm using latest Excel from Office 365 on server / desktops and main files I use are *.xslm (macro enabled) saved on a daily basis.

They all descend from the original BA templates years ago and have been saved from different versions of Excel with no problems at all.

Just make sure you have a back up of the file and watch it run to satisfy yourself there are no problems from saving/opening otherwise it could be a rant

[Derek27]

I won't start a new thread but I notice the BA spreadsheet templates are in Excel 97-2003 format. Can you save them in an up-to-date format? Does it matter?

I'm using latest Excel from Office 365 on server / desktops and main files I use are *.xslm (macro enabled) saved on a daily basis.

They all descend from the original BA templates years ago and have been saved from different versions of Excel with no problems at all.

Just make sure you have a back up of the file and watch it run to satisfy yourself there are no problems from saving/opening otherwise it could be a rant

Cheers foxwood. I doubt it makes a difference performance-wise but I find it irritating having that 'compatibility mode' in the Excel title bar, making you feel you're out of date.