Highline Excel Class 04: Style & Number Format & ROUND

We have a number of issues with the Normal style number format changing from General to date. I know that this is just a formatting change and how to fix it, but does anybody know how to stop it happening in the first place?

We are having to reformat sheets on an every other day basis at the moment as the number format refuses to stay as General.

I am trying to make this value:
23,825.71

Look like this:
23,826

Then i want to add ".00" to the end of the second step to look like this:
23,826.00

The problem is, when i attempt the last step, it drops the comma seperator and ends up looking like this:
23826.00

Hello All,

I want Excel VBA 2007 to format scatter chart data point (marker) line styles.

For example, I have plotted circles in my scatter plot that represent solder bumps on a flip chip IC. These "marker" circles are comprised of two parts--an inner circle (which one may adjust with "marker fill") and the outer circle (which one may adjust with "marker line style").

I want to fill these markers with white color, and change the marker line style to "dashed". In turn, I hope the bumps will appear like hidden edges in an AutoCAD drawing.

To fill with white, I've tried the following:
ActiveChart.SeriesCollection(1).Points(Count).Format.Fill.ForeColor.RGB = RGB(0, 0, 0)

VBA does not fill the circles with white. Perhaps white RGB(0,0,0)? If I change RGB values, e.g., RGB(255,0,0), VBA fills the circles with red.

To make the outer circle dashed, I've tried the following:
ActiveChart.SeriesCollection(1).Points(Count).MarkerLineStyle = xlDash

VBA does not like this at all. I've tried alot of variations, but I'm just really guessing on syntax, and VBA didn't like any of them.

I appreciate your help. As a novice user, I hope that I don't offend anyone with descriptions of what may be obvious to the general forum.

It sure seems to me that Microsoft makes alot of changes just for the sake of making changes. As always, they always add a gimmick that is nice, but overall, I prefer 2003 over 2007. I very much miss being able to use the macro recorder to figure out problems like this.

Thanks, and have a good day.
John

With this code

Code:

Sub clear()
Sheets(1).AutoFilterMode = False
End Sub

autofilter succesfully cleared,
but not succeed if the autofilter is on Table Format Style

what is the code to do this

thank you

Hi,

I am trying to write a VBA code to go through a range of data and and round any cell that has a number.

I am running into 2 problems and cannot get it to work. Please help
1. The current code does not round a cell that has a dollar sign even though the format of that cell is "accounting". Whereas, other cell with the same format "accounting" but without dollar sign gets round. I want it to round the one with dollar sign as well. How can I fix my code to do that?

2. I don't want to round any cell that has a formula. I can't seem to get that to work?

Your help is much appreciated. Thanks in advance!

Sub Round()
Dim Rng As Range


On Error Resume Next
Application.DisplayAlerts = False
Set Rng = Application.InputBox("Please select a range to be round with your Mouse then click 'OK' ", _
"SPECIFY RANGE", Type:=8)

On Error GoTo 0
Application.DisplayAlerts = True

If Rng Is Nothing Then
Exit Sub
Else

For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count


'If cell.HasFormula Then 'I want to check if it has formula do nothing, if it has number then round??? can't get this to work


If WorksheetFunction.IsNumber(Rng.Cells(i, j).Value) Then Rng.Cells(i, j).Formula = Application.Round(Rng.Cells(i, j).Value, 0)
Next j
Next i

End If
End Sub

Hi all,

I hope somebody can help me, I have a conditinal format (*CF) question (Excel 2010). I know how this works with value, but not with text.

I have a worksheet with customers in the rows and weeks in the collums.
If a customer has a promotion a letter of the status appears + the promotion mechanisme.
I already use the folowing CF:
Cell value begins with "b" ==> Blue
Cell value begins with "d" ==> Red
Cell value begins with "v" ==> Orange
Formula: =ISERROR(" ")

Excample of the file:

...................1.....2.....3.....4.....5.....6.....7 etc
account 1 .......... D ................. V1+1
account 2 .... D ........... D promo ............ V
account 3 .......... D3for2 ........................ D
account 4 ............................... D
etc.

I like to apply a conditinal format/ pattern style to all accounts from account 2 in the collum if there is a promtion / text in the week cell of account 1.

So it should look like this:

...................1.....2.....3.....4.....5.....6.....7 etc
account 1 .......... D ................. V1+1
account 2 .... D ....//..... D promo //............ V
account 3 ........../ D / 3for2 ........//............ D
account 4 .........//..................../ D /
etc.

So I like to start in cell B76: if(b$75=text than /// (pattern style)

I hope somebody knows how to do this.

thanks in advance for your help.

Kind regards,

Lenny

On my calculator, if I divide 462,534.05 by 335 I get 1,380.70. Then if I times 1,380.70 by 335 I get 462,534.50. Which is what I need it to be because I need to calculate the difference of a figure not dividing equally.

On excel, if I divide 462,534.05 by 335 I get 1,380.70. Then if I times 1,380.70 by 335 I get the SAME result of 462,534.05.

I have to account for the difference of not dividing equally so there is some format or accounting function that's preset that I don't want but I have no idea how to fix it or format my excel sheet to calculate as my calculator would.

Please can someone help? Thanks!

When you use format as table and use built-in style, you will see many shadowed (or background colorized) cells in your table. I couldn't find how MSoft did that. It is not background color (Fill Color) for sure. What then?

Hi -

I have a date listed as 1/21/09 and I need it to read as Jan-21-09 for a calculation purpose. I have already highlighted the group of dates and right clicked the mouse to select format and selected date - and then chosen the option of Jan-21-09. However, nothing is changing - it doesnt not seem to be recognizing the request.

There are about 700 dates that need to be converted- and without doing it manually - im wondering if anyone is aware of some sort of restriction that may be on the sheet that I can remove - In order to allow me to change the dates into this format?

Any feedback would be appreciated!

Thanks
Crystal