I ran a very simple code that conditionally formatted red any cell sum greater than 12. The macro worked all the way to the end - about 15k rows. After saving and re-opening file only the first 1603 lines had the formatting and the rest did not. Several attempts end with no change in results.
I did the programming in Office 97 but edited and ran it on Excel 2002.
I'm trying to use the SUMCOLOR function found on your website to sum cells which have conditional formatting (background colors with bold writing) applied to them, but it doesn't work.
I'm attaching the code found on the website as a reference.
I read some content on Cpearson but it's way beyond my understanding.
Function SumColor(rColor As Range, rSumRange As Range)
''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com
'Sums cells based on a specified fill color.
'''''''''''''''''''''''''''''''''''''''
Dim rCell As Range
Dim iCol As Integer
Dim vResult
iCol = rColor.Interior.ColorIndex
I know the VBA code to count cells from a data set that were manually colour-coded.
The problem is that the code (pasted below) doesn't seem to pick up cells that were coloured via Conditional Formatting. How do I do this? What is the VBA (if there is one)?
Function COLORCOUNT(varRange As Range, varColor As Range)
Dim cell As Range
For Each cell In varRange
If cell.Interior.ColorIndex = varColor.Interior.ColorIndex Then
COLORCOUNT = COLORCOUNT + 1
End If
Next
End Function
I found code online that I can put on my sheet to get my formatting properties to stay the same for the items in my dropdown list located on another page. However the code does not work for conditional formatted cells...which is what I need. This is the code that I have that will carry over regular formatted cells. Just not Conditional formatted cells.
View 1 Replies View RelatedI have a cell that calculates an etch time, eg 41.88. The cell is formatted to give me whole numbers and quarters only so that the operators input is made easier, in this case 42.
When I use a macro to copy this to another worksheet, although it appears as 42, the actual cell data is 41.88. Anyway I can get the actual cell data to be 42?
I have created a form in a worksheet which I have added questions too and then locked all cells except the ones where I want the answers in.
I have then added a button to the bottom of the sheet called "Print and Save". This work sheet is called "form"
On a second sheet called "database", I have all the titles of the questions running from a -> k and nother else.
What I want to happen is the user input the information on "form" into the boxes available. Once they have completed the questions, I want them to be able to click on "Print and Save" so that firstly the page "form" prints, and then for all of the answers to be saved in "database" below the titles for each question.
I have had a play, but just don't know where to start. Also, once there is a row of questions below the titles, I don't want it to overwrite information already there, it needs to go onto the next empty row available.
Attached is my excel file.
You will see in the code that I have the code for printing.. I just found this on the net and it seems to work fine for me.
I have data which needs to be formatted with a thousand separator as below.
400.00
1,000.00
250,000.00
How can I do this without it displaying as below
,400.00
I do not want it reflected in the case of hundreds.
I have a graph that pulls data from a toggled list. Values include currency, percentages and general numbers. The data is set up as text to report as currency, percentages and general numbers. Is it possible to have a graph read these text values?
View 1 Replies View RelatedReceive worksheets with data in different layouts that needs to be moved into formatted worksheet with unique layout and field size to import in to Access database.
Example of formatted table layout ....
I'm trying to lookup data in a cell formatted as time, the array is also formatted as time but I continue to get the #N/A. Is there a certain format that I should use?
View 9 Replies View RelatedI'm trying to lookup data in a cell formatted as time, the array is also formatted as time but I continue to get the #N/A. Is there a certain format that I should use
View 6 Replies View RelatedI have a data entry field on a form that is formatted for percentages. Sometimes when i key in "10" it will correctly convert it to "10%" but other times it randomly converts it to "1000%". When the error occurs, I'm able to correct it by re entering the value a few times until it displays correctly as "10%".
View 2 Replies View RelatedPart of my work involves the transfer of thousands of lines of Excel data into FileMaker and then exporting this data to Word where it is finally formatted for publication. What I'd ideally like is to skip the FileMaker step and simply have it so that I import all the data from my Excel file into a Word document in one swoop. The problem is that the eventual export can't feature tables, it would be the icing on the cake for the data to remain as formatted, and the data needs to read line<space>line<space>, vertically.
Essentially, I need to know how to take this from Excel ..
this1.jpg
And turn it into this in Word ...
this2result.jpg
How to set up an excel sheet to automatically save data after every data entry? Or maybe after a specified time period?
View 1 Replies View RelatedIs it possible to have a userform open in a workbook and when the data from the form is saved, save it to a completely different workbook. If so, will that other workbook need to be open for the userform data to be saved?
View 5 Replies View RelatedI have created one Info path solution to gather information from different locations to one hidden Access database. And in last month or two I already have more than 30 000 entries in database. On other side I have created 2 excel workbooks, one for purpose of report that has all connection done through VBA, user just opens it, clicks button and gets report and one workbook that is for other set of users so they can change 2 specific columns of data, everything else is locked. When one department fills value it should fill based on gathered information, and clicks save that cell or cells are being locked.
At first I did on worksheet change event, when user changes a cell (enters information) it triggers worksheet_change event and saves that value in database and colours cell green. But, then users start complaining that when they use copy paste or the use fill paste option (just pull value from one cell to others) it does not save value. Ok ... that is because worksheet_change event does not trigger in situation when copy paste in any form occurs.
So how to solve it ... when pulling data in excel I pull identical table (data) in other hidden worksheet. When they change what they need to change, they click button "save to database" i have created and there is a macro that makes other sheet visible, goes through the records and saves where cell value in table that is being entered is different then same cell in hidden table.
Problem is that sometimes my code works without a problem and sometimes they get an error. I cannot figure out when. When a lot of users use that excel file, for some users it is a read only file, and as I presume that is ok, because macro works in any case and data are stores in database so excel file itself does not have to be saved.
Here is code.
[Code] .....
i get an excel file with dynamic data everyday. is there away i can use a macro on this file to store this dynamic data to a specfic closed workbook and add the data onto the old data in that workbook?
View 4 Replies View RelatedIs it possible to have a userform open in a workbook and when the data from the form is saved, save it to a completely different workbook. If so, will that other workbook need to be open for the userform data to be saved?
View 4 Replies View RelatedI am working on a project in which I used a formula and created a kind of a software.what I need to do is that after my user enters certain data it is sent to a worksheet(as directed).now I want that data to remain saved there even when I am changing the inputs in the formula. what is happening is that my previous data is getting erased when I enter new variables to the formula .
View 1 Replies View RelatedI am trying to automate the cleaning and compiling of some data, in fact its quite a lot of data, there will be about 200,000 lines over several spreadsheets.
Determine the width of the data on spreadsheet1
Determine the rows with empty cells at the bottom of spreadsheet1
"now that I know the dimensions of this rectangle"
Copy a rectangle (with the same dimensions as the one on sheet1) of data from sheet2
And paste this rectangle into sheet1...
"This is the point of the excercise, I cannot have any gaps in the data (this includes at the end of a spreadsheet) as it needs to be a continous flow from one shreadsheet to another"
This is what I have done so far... I understand if it is laughable, I've only been doing this a couple of days.
Sub HowMuchSpace()
' No. of columns
Dim bWidthOfColumns
Range(Worksheets("Sheet1").Range(":*", ":*").Cells.SpecialCells _(xlCellTypeConstants).Count).Value = bWidthOfColumns
'No. of rows
Dim lNumOfRows
Range("*:*").Value = lNumOfRows
End Sub
I am trying to lock any cell within a set range that contains data but I want this only to be activated when the user saves the file.
View 3 Replies View RelatedI have a spreadsheet that imports data, manipulates it then deletes 2 of the sheets then saves the file under a different name to the network. Is there any way to save this new worksheet without it storing the macros - so when the user open it, only the data is there and they get no prompt to enable macros?
View 6 Replies View RelatedI've got a spreadsheet listing applications on PCs (assets), one line per application, and need to import that info into another document for each PC and save it with a file name which = the PC name (asset number). I've attached a couple of sample files to try to explain what i mean.For asset number PC037014 I need the corresponding fields filled and the list of applications pasted under where is says Additional Software List (inserting more rows if there are too many applications to fit in). Once the information has been populated, i'd like the file to be saved in a specific folder then move onto the next asset number in the list.
View 13 Replies View Related[Code] ....
User 1 will enter date in column AA, then data needs to be copied to column CY (data in CY must not change again). I have a if formula that check if any AA and CY is the same. If any changes was done, it will give the cell a "YES" trigger to send a mail.
I think the .value is incorrect.
I currently have a tab on my spreadsheet named CSV. I have a macro that when called saves the information on that tab and sends it to a folder saved as a CSV file.
However this tab has a lot of information on and i want it so send it with only the information needed at the time. the data i want to send is in Columns A to AM and has rows down to 7392. I only want it to save the file with the data in the rows that has "Export" in Column AQ ignoring any other rows of data that i dont need it to send
I dont want anything to happen to the CSV tab ie delete lines etc as files will be getting sent regulary from that sheet still but with the data constantly updating in different cells
I have a macro used to save a worksheet, its placed on the DATA sheet.
is there a way so that when the user clicks this button each row 12:200 is checked for empty cells in that row (cells B:P). If a row is found the ref number in column A of each row is placed into next available row in Sheet3
For example :
A12 = 12345
A13 = 6789
A14 = 9876
A15 = 54321
the DATA sheet is populated with various data in rows 12:15
it is found that B12, L12, E14,F14,P15 all have no data, so the values of A12,A14 and A15 are placed into next row in Sheet3
Result in sheet3
A2= 12345 (valuse of A12 in DATA Sheet)
A3= 9876 (value of A14 in DATA Sheet)
A4= 54321 (value of A15 in DATA sheet)
Finally a message box to state there is missing data, for example
"there is missing data from refs 12345 , 9876 , 54321" ie the list pasted into sheet
I am wondering if there is a way to take the charts I have created, and save them on a different worksheet in Excel. The issue I have is that when I erase the data source, the charts lose all their data. Is there a way to save the chart as is without it relying on its data source (ex. So I can remove the data for the chart)?
View 2 Replies View RelatedI am looking to save a range of data from an input sheet, to a data sheet.
I know how to save indivdual cells to a worksheet in the form of a data
what i am looking to do is save A6 - J6 down A19 - J19, but only saving the data which is entered
A6 - J6
A7 - J7
I've tried to remove connections after saves and have been unsuccessful.
HTML Code:
' Delete Additional Connections
' If ActiveWorkbook.Connections.Count > 0 Then
' For i = 1 To ActiveWorkbook.Connections.Count
' ActiveWorkbook.Connections.Item(1).Delete
' Next i
' Else
' MsgBox ActiveWorkbook.Connections.Count
'
' End If
HTML Code:
' Remove named ranges
For Each nm In ActiveWorkbook.Names
nm.Delete
Next nm
I've tried both of these and the Excel file is still attached with XML to the query table. I get an error message saying the format is different then if you click okay it shows its connected to the database still. Is there an aggressive command that will severe all connections before saving? What commands to run to stop this connection after save to another workbook?
I have a worksheet in my workbook that contains data for 25 different airfoils, each one in a cell space of 3 columns and 50 rows.
In order to do some analysis on these airfoils, I need to save each 3x50 as separate text file to define input for analysis code.
How can I do this in VB in one macro?