Cell Formatting Does Not Refresh Cell Data
Jun 6, 2006
When using the format cell function, I have found some cells don't respond to the format until I enter and exit the cell (or edit). Other cells with the same data change when formatting is applied. The cell format is being changed from text to decimcal number. I have a few thousand records and about 30 cells and about half of them change when the format is applied. It would be impossible to manually edit each non-responsive cell to change the format.
View 3 Replies
ADVERTISEMENT
Jul 13, 2006
I searched, but couldnt find a thread which covers a VBA code which covers writing a conditional format into cells and changes each time contents of the cell are refreshed. The conditions are:
If activecell.value < 0 Then
.interior.colorindex = 3
If activecell.value >=0 Then
.interior.colorindex = 4
and i want this to happen for a big range of cells (range(a1:k1500) So basically, once the macro is run(which has the code to insert the conditinal formating for all the cells), if the user makes any change to the cell(within the range), the color of the cell should change automatically(like Conditinoal formating works by default). Prior to this, i was doing my cell coloring using the 'for loop' approach, but that of course does not reflect once the macro has ended.
View 2 Replies
View Related
Jul 27, 2006
i have an excel spreadsheet with 27 or so workeets. it contains sales figures in it.
I want to be able to link mutiple cells of this workbook to another workbook so that it retreives that data, So that when I hit the refresh (!) button it will automatically put the data in. I will recieve new sales figures (new files) on a monthly basis so i want it to be able to update the figures to the new figures.
View 4 Replies
View Related
Sep 17, 2009
I want to refresh a cell by triggering an event using VBA code. I have tried to refresh cell range value using this below code.
View 7 Replies
View Related
Oct 23, 2012
I had below data in excel which i need in one cell with some formatting. I had lots of lines like this, some of the input are below
JT12345
JT12346
SB06
SB07
SB 08
SB 09
I want output in one cell say in A1 cell like 'JT12345','JT12346','SB 06','SB 07','SB 08','SB 09'
Sometime the data might be only 6 letter or 4 letter or 4 letter with space in middle.
View 9 Replies
View Related
Jun 22, 2009
I have a sheet named "Risk Register" and another named "Outside Residual Risk Threshold". I need an event macro on the worksheet "View code" section, so that any time a value changes in column AF on "Risk Register", the filter on rows 8:39 in "Outside Residual Risk Threshold" is refreshed. Both sheets are protected.
The code I tried in the "view code" or "Outside Residual Risk Threshold" was as below...
View 7 Replies
View Related
Oct 19, 2008
I'm having a little trouble figuring out how to do some conversions. For instance, I would like to know how to convert a decimal number like this:
1.800
which is a ratio of 1.800:1 to display like this:
9:5
So, when the 1st value changes to something like this:
1.778
It would be displayed like this:
16:9
Likewise, a ratio of 1.333:1 would be displayed as 4:3.
I also don't want it display in Time format as Excel has a habit of converting values with colons to time format.
View 4 Replies
View Related
Dec 18, 2013
I have a spreadsheet that we use to generate visual data, with very small column widths. I have a column width of 1 at the moment, and some data displays, while some doesn't. I want it to show a number which appears every 5th column, without having to merge and center each set. I used to use a macro to merge and center across the data set but the data set length is now dynamic so that is difficult.
View 1 Replies
View Related
Sep 24, 2013
I have one column that contains a monetary amount (column AQ) , and another that contains text reading either "inflows" or "outflows" (column AC)
When AC says "inflows", AQ should be positive, and when AC says "Outflows", AQ should be negative.
I need the text in column AQ (the monetary amount) to become red when the the opposite is true.
i.e. When AQ is negative and AC says "Inflows", AQ should become red. And when AQ is positive and AC says "Outflows", AQ should become red.
View 2 Replies
View Related
Feb 26, 2013
I've been given some data and the date column is formatted as 'general'. If I amend it to the date format I prefer and click ok nothing changes. If I then click in the cell and press enter it changes the format to what I want. Is there anyway of refreshing all the cells so the format change takes effect?
I can achieve what i'm after by using find and replace to change '2012' to '2012' and '2013' to '2013' but i'd be interested to know whether there is a more recognised solution?
View 2 Replies
View Related
Sep 22, 2009
have come across the apparent inflexibility of a pivot chart retaining its user defined formatting whenever it is refreshed.
I understand I can save my chosen formatting as a 'user defined' custom chart, which can be applied post-refresh, but was wondering if a simple bit of code would do this automatically, saving the additional clicks!
how to deal with the attached. How could I adapt the code to call in other 'user defined' charts I may create in the future?
View 8 Replies
View Related
Dec 23, 2008
I have a dashboard that I created that is driven off one source of data. I have several lists and pivots created from that single source. I have two cells that are driven off of lists that then drive the pivot table. When a user selects an item from the drop down list I would like the pivot table to update. I have two tables like this on the main dashboard. Once the user selects the first data point, the table refreshs as does the second drop down list. After they select the second data point, the second privot table will refesh. Manually everything works prefectly, but when I attempt to automate it with VBA, it will only update the initial pivot and the secondary list, but not the second pivot.
I have posted the current code below.
"SelDept1" and "Wave" are the two cells that contain the drop down lists.
Private Sub Worksheet_Change(ByVal Target As Range)
Application. ScreenUpdating = False
Sheet4. Unprotect "lcssdi"
Sheet2.Unprotect "lcssdi"
If Target.Address = Range("SelDept1").Address Then
Me.PivotTables(1).PivotCache.Refresh
End If
View 3 Replies
View Related
Jan 29, 2014
I would like to be able to change the color of fill in a cell if there are 3 occurrences in cells. Attached is a sample.
In the attachment, if the cells A2, D2, G2 all have a number "1" in them I would like cell K2 to have a color fill for the cell.
The same would be for the other cells, if B2, E2, H2 all have the number "1", K2 should have a fill in the cell as well, same as if C2, F2, I2 have a number "1", fill K2.
I only want the fill color to be in cell K2 if all 3 of the cells, ex: A2, D2, G2 have a "1" in them. Also, some of the other cells may have a number "1" in them but I would not want them counted unless it is 3 of the consecutive cells.
View 7 Replies
View Related
Oct 28, 2013
I have a piece of code that runs through various excel files and takes the data (minus the header) and pastes that into a separate workbook. The piece of code that does the actual copying i think is this (i have used code from the msdn website)
Code:
With sourceRange
Set destrange = destrange. _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
I have tried replacing the sourceRange.Value with sourceRange.Text however when i do that it will copy nothing any more.
View 2 Replies
View Related
Jun 18, 2012
Excel 2010. I have a field that is color formatted based on the value of another field, which is calculated.
The conditional formatting does not refresh when the value is recalculated, but when I go away from the tab and come back, it works fine.
Any simple VB code to refresh the conditional formatting upon a workbook change?
View 1 Replies
View Related
Jul 10, 2014
Assuming I have three worksheets:
1) input: col A has a series of product code (string)
2) calculation: where I want to automatically copy and paste each product code from "input" worksheet into "calculation" cell B4, and through many steps of calculations, return the outputs in cell B8 and B9 (parameters are numeric)
3) output: how to automatically record B8 and B9 from "calculation" sheet to this sheet Row 5 and 6 from each loop? (format of output doesn't really matters)
View 4 Replies
View Related
Nov 4, 2012
I have a spreadsheet where I have a column of dates and I want conditional formatting to highlight the cell red if the date is less than or equal to today but if the cell is blank to do nothing.
At the moment I have the following formatting applied using the "format only cells that contain" option I have cell value less than or equal to =TODAY() except that obviously highlights every cell red that doesn't contain a date. Is there another conditional format I can apply in addition to this that will not highlight the blank cells ?
View 4 Replies
View Related
Feb 11, 2010
I have inherited a monstrous speadsheet on which all data appears on a single sheet rather than being broken up across several sheets in some logical fashion.
One portion of this monster is a set of quite a few form letters, each of which reference various cells elsewhere in the spreadsheet.
I want to move all of these letters, together, to another tabbed sheet as a first step in reorganizing the monster.
Simply copying and pasting the cells or columns doesn't work. It fails in different ways depending on which pasting options I employ.
A very simplified version of my problem appears on the 5 sheets of the attached file, with what I hope is just enough further detail about the difficulty.
By the way, I also tried using the "Insert Copied Cells" option when pasting but since this failed with the exact same results as one of the other options I didn't include the results in the example shreadsheet.
View 5 Replies
View Related
Dec 12, 2012
I have a table that is a list for validating values in a column .what I want to do is instead of getting an error message anf stopping if the entered value is not valid the cells become red.
View 6 Replies
View Related
Apr 11, 2014
I'm running into a problem when trying to use INDIRECT in combination with Conditional Formatting Data Bars.
Basically I want to show a Data Bar in a certain cell based on the value from another cell.
It would be easy if I could simply copy the value from the other cell across into the cell in which I want to show the data Bar. Unfortunately I cannot do this as the values in the cells where I want to show the Data Bars can change.
As a solution when using Conditional Formatting Data Bars I figured out I can simply do the following:
> Under 'Minimum' use 'Type': Number with 'Value': Zero.
> Under 'Maximum' use 'Type': Formula with 'Value': =INDIRECT("B'&ROW())
This will pick up the value up from column B in the same row.
> In column B i then have this formula: =A1/C1. In C1 i have the percentage that the Data Bar should be using. By dividing the value in A1 with this percentage B1 will return a value that is used for the "Maximum" setting which will then ensure the Data Bar will display correctly.
For example: If A1 value is 4 and C1 value is 25%. Then the formula in B1 will return value 16. The scale used for the Data Bar is then 0 to 16. Seeing the value in A1 is 4 the Data Bar will correctly show 25%. If I then change the value in A1 to say 2 the scale used will change to 0 to 8 and the Data Bar will still be showing 25%, which is exactly what I'm after. So effectively this means a value from another cell is used to work out the Data Bar.
So all is fine up until this point. The problem arises when I want to apply this same logic to the rest of the column.
If I change the 'Applies To' to the range I want it applied to all the "Maximum" will only be based on the top row value. I'd expect cause I'm using the INDIRECT reference with ROW() it would have picked up the values from each individual row. I could create a new CF rule for every row, but that's not practical as there are way too many rows.
Why does the INDIRECT function with ROW() not work in this case? How to make the CF rule copy down properly?
View 3 Replies
View Related
Mar 19, 2014
I'm importing data from Excel 2010 into a database.
I'm joining to 2 cells into one. A1 = 12 Rushwood Street and B1 = London. So C1 = 12 Rushwood Street London
I need C1 to look like this:
12 Rushwood Street
London
I tried wrap text but when I import it comes out as one line in the database.
View 3 Replies
View Related
Jul 20, 2012
I have a Workbook (Test 1) with several columns A-S. I'd like to be able to continue to add data into Test 1, and have some of the data pulled into a new Workbook (Test 2). Basically pulling data from Test 1, cols. D, L, J, E, I, C, K, Q and S - into the new workbook Test 2.
Some of the columns in Test 1 have conditional formatting and data validations, but I need the data in these columns to be replicated into Test 2 - without having to copy/paste between the 2 workbooks.
I figured how to do this in Excel 2010, but my office is still using 2007 and I can't seem to get it to work....
View 1 Replies
View Related
Oct 9, 2013
I have two columns. In column B is the date of "last check". I column A is the date of "next check". I would like to have cell A2 in yellow color 334 days after the date entered in cell A3 and than in red color 365 days after the date entered in cell A3. Same thing for cell B2 related to date entered in cell B3. Yellow color in cells announces that check will expire within 30 days and red color that check has been expired.
View 1 Replies
View Related
Apr 14, 2013
This is a conditional formatting problem I have not been able to resolve:
I have a range, say A1:N30
Each cell contains a text/number combination.
I would like to highlight each cell on a specific row if the vale exists within the row above - the issue is that the cell values are not in the same columns.
I have attached a sample workbook with the desired output.
Conditional formatting.xlsx
View 2 Replies
View Related
Apr 1, 2014
I am trying to alert our purchasing mgr when order dates are approaching or not meeting our project deadline.
As of now i have the following rulesif order date is due today or past due - redif order date it greater than project date - redif order date is due within 2wks - yellow
Now all I need is a rule where there is an order without a due date but the project deadline is within 2wks (yellow) and past due or due today (red)
KO_04.01.14.xlsx
View 3 Replies
View Related
Aug 12, 2014
I have a column who's content is determined via about 6 nested if statements from data on that row. That cell is then conditionally formatted to a certain color based on the text that is ultimately printed from the nested ifs (simply an extra visual legend for the text). All of this contributing info and about 1000 lines items make a very large and difficult to print page. What I am trying to do is a make a summary sheet that simply takes the index number of these 1000 rows and copies or links the conditional formatting of the mentioned cell onto this number on another sheet. I have already linked this status cell and put it adjacent to the index number which works well in that with two columns I can show the index and status but if I could combine the color of the status cell onto the index cell, it would be even better. I am pretty sure another conditional format for this summary sheet would not be possible or be extremely complex since the contents of the index cell I am conditionally formatting have no bearing on the conditions for the format. Was hoping there is some VBA magic that could simply mimic the conditional formatting from one cell and put it on another.
View 1 Replies
View Related
Aug 13, 2008
I have Excel 2000, recently when I format a cell to display mm/dd/yyyy and enter mm/dd/yy, it is displaying dd/mm/yyyy. Or it starts out correctly and during a future opening of file it displays incorrectly.
View 2 Replies
View Related
May 9, 2013
I'm using Excel 2010 and I would like to format a cell (say, the font of that cell turns RED) if the value of another cell meets a certain criteria.
View 9 Replies
View Related
Jul 28, 2009
I would like to format cells with a currency based on a value selected at the top of my worksheet. Cell C3 is a drop down of currencies. And I would like cells range E11:E200 to format in whatever currency selected in cell C3.
View 9 Replies
View Related
Mar 22, 2013
I'm trying to analyse all of my bank statenments to see where all my money went!! I've got online banking so I thought it would be a relatively easy process of copying and pasting each month into excel.
Well, that in itself was easy, the problem is with the formatting of the financial numbers. Excel doesn't seem to recognise them as numbers, so at the moment I can't do any manipulation with the numbers. I have tried everything such as:
Format Cells > Number > Number and Currency
Copy and Paste Special > Values only
Copying into Notepad and back into Excel
Even if it appears to have changed the numbers to 'number' or 'currency' formatting by right justifying the numbers, if I try to add up the numbers using the SUM function, it doesn't want to know.
For some reason nothing seems to work - what am I missing?
For an example, I have attached a snippet of the spreadsheet with my starbucks transactions.
View 3 Replies
View Related
