my excel setting... I'm not able to see some of the numbers (in currency/accounting) in excel file, it shows as many "#" sign, and it's not due to the column width. The same file can be seen in other computer.
The format setting is the same in all the cells but only certain rows/cells' numbers appear like "normal".
I´m having aproblem with Excel 2007 about Conditional Formatting. I have a row of Dates for example 02-01-2009 03-01-2009 04-01-2009 , etc in different columns.
Then what I want to do is use Conditional Formatting to Format cells on several rows below according to the day (if its weekend paint red, if not, dont do anything). I'm using the "Use a formula to determine which cells to format" and the condition is (supposing the cells with dates are A1 to C1)
=WEEKDAY(A1:C1) > 5 .
So with those 3 dates provided lets suppose Januar2nd is not a weekday, so the outputIwant is: Red White White, (Next Row) Red White White, etc for several rows.
Now what I think is not right is it only works for the row in which I have the cursor so its like: (Lets imagine I selected 3 rows on which I want to see that output) Red White White (The row in which the cursor is works fine), but the next row goes Red Red Red and the third the same.
I created a macro on an Excel spreadsheet:
Sub Macro1()
Range("C11:D19").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
Range("E5:E20").Select
Selection.NumberFormat = "0.00%"
Range("D24:E43").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
ActiveWindow.SmallScroll Down:=24
Range("D58:E339").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
ActiveWindow.SmallScroll Down:=57
End Sub
When the macro is run, it works with the exact amount of data that I've entered.
However, because I have specified a specific range, if I add a row or column of data to the spreadsheet and run the macro again, it throws the whole thing off (certain cells get formatted when they shouldn't and others aren't formatted)
How do I tell the macro to look in the spreadsheet for varying ranges of data and format those cells?
Code is trying to
1. Where there is data in column B
2. Goto first row of data
2. If column J = "Closed"
3. then select Cells from column B to J in that row and colour these cells in red
4. If column J= "Open"
5. the select Celss from column B to J in that row and colour these cells in grey
4. Repeat for next row until you get to cell in column B where there is no data.
Recieve run time error 1004: Method 'RAnge of object_GLobal failed
===============================================================
Sub Colourclosed()
Sheets("Risks").Select
Dim LastRow As Long
Dim i As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For i = 8 To LastRow
If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select
Selection.Interior.ColorIndex = 3
[code].....
Note: I've managed to do it for one cell ie for Column B by replacing "If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select with "If Range("J" & i).Value = "Closed" Then Range("B" & i).Select" This works but only colours in cell in column B, how do i do this so it colours range of cells
I'm trying to set a range variable ('IngFore') as equal to a number of rows long and a number of columns wide via 'Cells'. The range needs to be dynamic in that the total number of rows and columns will vary over time. Hence, I've defined 'lastdate' to determine the last row in the array, and 'lastcol' to determine the last column in the array. The starting point for the array is I12.
It would appear that I can't quite get the syntax right.
Code:
Set IngFore = (Cells(12,9 to lastdate,9)),(Cells(12, 9 to 12, lastcol))
I've tinkered with it here and there, removing and adding brackets but I can't get it to work.
I am dividing the number. I have for example 20 hours(A1) which have to be divided into 8h + the rest.
So, 8(B2)+8(B3)+4(B4) hours. I have tried few things, but I can only divide it by 8 and it doesn't go anywhere from that.
I am trying to conditionally format the top middle and bottom thirds of a range of data. Problem is, that the range needs to be flexible as sometimes there may be a maximum of 36 cells with data, but sometimes there may be less (so there are blank cells in the range that need not be counted). The methods I have tried always include the blank cells, and so it is not equally formatting the thirds (as it includes the blanks cells as part of the bottom data)....
Here are the 2 methods Ive tried so far using excel 2003)
Top 34%:
=IF(INT(COUNT($D$3:$D$38)*34%)>0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*34%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*67%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*100%)),MAX( $D$3:$D$38))
I have a column of numbers and want to make sure everything has been entered correctly from our scanning software. Basically, I want to automatically highlight any cell that has any letter in it (e.g. z12o2 instead of 21202 or R705 instead of 5705), ignoring any cells that contain only numbers. I haven't had any luck using conditions based on formulas like =ISTEXT.
View 2 Replies View RelatedI am comparing a set of common variables across 2 sets of independent financial data. When comparing a variable across the 2 sets e.g. profit I want to colour a neighbouring cell based on the size of the difference. For example if the difference between the two values is within plus or minus 1 make it green, plus or minus 5 red etc.
View 5 Replies View RelatedI have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.
View 7 Replies View RelatedI have data on 400 rows. Each row has a maximum of 10 cells with data, but many have empty cells with no data. I would like to sort each row to show values of cells in sequence and eliminate empty cells. I can use the sort row function but its a long process for 400 individual rows. Is there an easier way?
View 1 Replies View RelatedHow can I format cells to contain Minutes, Seconds and Hundredths of seconds to be used in calculations eg 1.24.99 means 1 minute and 24.99 seconds. Example calculation is: 1.24.99 - 1.24.90 =0.0.09
View 2 Replies View RelatedHow do I force the Date and Time format when I import data into an Array that is listed in a listbox?
The desired format is YYYY/MM/DD hh:mm and I do not want to be dependant on the system settings.
i am doing some calculations using vba in excel, i need to know how to set the cell format to 'accounting' in vba ?
View 2 Replies View RelatedI've been struggling with this for a while now and can't believe how hard it is!. I've searched on this site and on others to get some clarification but to no avail.
It's pretty simple really. I have a user form which contains a tex box for a user to input the date I want the format to be dd/mm/yyyy but can't find out how to set the format of the text box to this.
I have a dynamic chart which I would like to show markers in a SeriesCollection anywhere the backcolor of any of the corresponding source cells is ColorIndex = 36; otherwise no marker is shown. Something along the lines of:
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred
when copy/pasting, but IS if the keyword uses "quotes".
With Sheets("Chart").ChartObjects(1).Chart.SeriesCollection(1)
For i = 1 To UBound(.Values)
If "something here to check if .Interior.ColorIndex = 36" Then
.Points(i).MarkerStyle = xlMarkerStyleCircle
Else
.Points(i).MarkerStyle = xlMarkerStyleNone
End If
Next i
End With
Is it possible to change the contents of a cell (A1) displaying a name as:
John Smith
to Smith, John (in cell B1)
I have file 1 and file 2.
In file 1 I have clicked:
Format cells > Text and
Data Validation > Text lenght = 5
Problem is when I copy from file 2 to file 1 i lose this above mentioned format.
The attachment contains a small extract from a database, and an associated PivotTable. In the database certain cells contain 1 and are coloured, others contain 0 and their font colour is white. In the original these cells all contain formulae. I want my PivotTable to pick up only cells with a 1 (or more). So, in the example, only nine of the rows are useful and I don't want the other six. The rows I want of course depend on the year I select.
View 2 Replies View RelatedI have a spreadsheet that contains data with the following format:
5 EA/BX
12 EA/PK
Unfortunately, sometimes the data is input without a space:
5EA/BX
12EA/BX
I am looking for a way to insert a space between the data when one does not exist. (I cannot have more than 1 space between the alpha numerical character and the numerical character.)
Is there a way to format some particular colums so that when i enter my data into the cells i do not have to keep changing the caps lock to uppercase or pressing shift key which slow down my typing as i am not very fast typer.
View 4 Replies View RelatedI concatenate value of the several cells at one. (A1&"/"B1&"/"&C1 and etc. The length of characters may be different and I want it be looking neat by adding spaces. Something like that:
400/ 25/ 30
40/250/300
-I have an excel export from a database.
-The export has multiple columns of data.
-The column I am interested consists of numbers and some text.
-The numbers are preceeded by "zeros", e.g. "00054" or "0021"
-The text is usually "N/A"
Problem:
-The column cells are formatted as "General".
-I need to get the "00054" to just "54"
-I am looking for a solution other than re-writing the column by hand (hundreds of column entries)
I've Tried:
-Changing column format to "Number", but the "00054" format remains.
-Played around with other cell format types
-If I double click the cell, the entry then goes to the format I need!!! e.g. "00054" -- double click-- "54". Wallaaaaa... I will get carpal tunnel if I have to double click all these columns entries!!
How do I go about formatting selected cells for different colors for each different number in the selected cells? If I have numbers scattered about I want the cell to show a different color depending on the number that is inserted. EG: 115=red, 365=blue.
View 2 Replies View RelatedHow to proper set the Format cells for this?
Time returns always
2:01:23 PM2:01:26 PM
I would like to have
14:01:23 14:01:26
I have some imported data that Excel does not recognize as date or time so I have to edit each cell pressing F2 and the enter to "convert" the value.
I have about 120.000 records and the macro takes long time to do the job.
I have a spreadsheet that uses a macro to format it. It’s quite simple and the code is shown below:
Code:
Range("A1:D2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
[Code] ...........
The spreadsheet will have info constantly added on (downloaded from a CSV).
What I would like the spreadsheet to do is look at the cells and only format those that have data in them but are not already formatted – otherwise I’ll just end up re-formatting everything every time and as the spreadsheet gets bigger it'll take longer and longer.
I have a couple Excel files that exhibit a very odd and annoying behavior. When I save the files, everything is fine and all cells are formatted as they should be (e.g., general, text, numbers, etc). However, when I open the file again, all cells are formatted as date. This is quite annoying since some underlying data will be destroyed.
I already checked if the format style "normal" is damaged, and indeed it was (the standard format was set to date). However, I changed this back to general before saving, yet still see all cells formatted as date after reopening the file (this does not happen consistently... most of the time everything is fine but occasionally I get the date mess-up).
I have a group of cells in E2:E4 that are 144.00 in, 240.00 in, and 72.00 in and are formatted as general.
I get these values when I export data from an AutoCAD program to Excel and I want to sum these values to get the total length.
I want to sum the three values and have the result be the same (i.e., ###.## in). The number of values can go to 30, so I need a simple line of code, =SUM(???)
data is stored in the rota sheet, rota number in coloum B and the name in coloum C the code compares the contents of colum b in rota sheet to coloum B in the other sheets then the name from coloum c in rota sheet is inserted next to the corisponding rota number for the next 7 days. i want the cells in sheet monday tuesday ect to format themselves to yellow if the name is omited in the rota sheet i cant seem to get the code to jump to col when the cell is empty i've highlited in red the parts giving me a problem unfortunatly i cant upload the sheet as its over the size limit
Sub FIND()
START:
WORK = 0
DA = 0
Line = 0
Sheets("ROTA").Activate
RO = RO + 1
RA = ("B") & RO
Range(RA).Activate
ROTA = ActiveCell
ActiveCell.Offset(0, 1).Activate
Name = ActiveCell
If RO = 200 Then Goto Quit
GH:
LIN:
Line = Line + 1
TREE = ("b") & Line
Range(TREE).Activate
If ActiveCell = ROTA Then Goto NAMED
If Line >= 200 Then GoSub LIST
If DA = 8 Then Goto START
Goto LIN
NAMED:
ActiveCell.Offset(0, 1).Activate
ActiveCell = Name
If Name = ("") Then GoSub COL
ER:
Goto GH
Exit Sub
LIST:
Line = 0
DA = DA + 1
If DA = 1 Then Sheets("SUNDAY").Activate
If DA = 2 Then Sheets("MONDAY").Activate
If DA = 3 Then Sheets("TUESDAY").Activate
If DA = 4 Then Sheets("WEDNESDAY").Activate
If DA = 5 Then Sheets("THURSDAY").Activate
If DA = 6 Then Sheets("FRIDAY").Activate
If DA = 7 Then Sheets("SATURDAY").Activate
Return
COL:..................
