Cell Number Format Based On Another Cells Value
If Cell A1 in my spreadsheet is 1, then cell A2 should appear formatted as a percentage. So if a user enters 9000 into cell A2, it should appear as 9000%. If Cell A1 in my spreadsheet is 2, then cell A2 should appear as a comma-formatted whole number. So if a user enters 9000 into cell A2, it should appear as 9,000. Conditional formatting doesn't appear to let me do this.
View Complete Thread with Replies
Related Forum Messages:
Number Format Based On Corresponding Cells Text
Cell L90 to L120 are input cells where I type " Percentage" or "Dollar Amount". I need the five cells to the right of each input cell starting two cells over, to be formatted accordingly. I read a thread similar to what I'm asking for suggesting a worksheet change macro, but I can't quite figure it out for a range of cells. Also, in case I need to add a row to my sheet before all of these cells, how can I make sure that this will still work without having to go into the code. Is this an activecell thing?
Format Cell Number Based On Value
I need to evaluate some offers and for the management I need to follow some strict rules like:
1. € 1.000.000 instead of €1000000. But that's not the problem
2. to use maximum 3 digits... Example: 10,2 or 6.12
I tried with an if function and then round, but I think that's not right way. If number < 10, then round with 2 decimal places else, round with 1 decimal place
Change Number Format Based On Another Cell?
I have used this forum periodically and it has saved my bacon on numerous occasions so I know someone can help with this!
I created a workbook for staff planning and it's becoming a behemoth!
I'm wanting a range of cells within a row to change the number format to "[hh]:mm" based on a value in the 2nd column of that row. I have played about with a few things and can't seem to get it to work. What I have so far is this:
Conditional Number Format Based On The Value Of A Specific Cell
I have a large spreadsheet which includes many rows whose number format needs to change depending on the value of a specific cell.
So for example:
If cell B20 has value "A", cells Q20:AG20 should display as 1,234
If cell B20 has value "B", cells Q20:AG20 should display as 5.6%
If cell B20 has value "C", cells Q20:AG20 should display as 7.89
As conditional formatting does not seem to include a number format option I've worked out that I need some sort of macro to change these settings for me, either automatically whenever the value of B20 changes or, more likely, when I manually choose to run the macro.
Conditionally Change Number Format Based On Another Cell's Value
Format the number in Column AL to '[hh]:mm' when Column C's value is ="P/T", otherwise format to 'General'
Column C's value is referenced from another sheet in the same workbook via a VLOOKUP function.
Column AL's value is based on an IF formula which goes like this:
Basically the above formula asks if Column C's value is F/T then count (because if F/T then the corresponding values in that row are whole numbers). If not, then SUM (because if P/T the corresponding values in that row are set to [hh]:mm format).
I have another sheet in the same workbook which has code (quoted below) which does something similar but I don't understand it enough to get it doing what I want for the sheet in question.
Format Cells Color Based On 1 Cell
I want to format a group of cells color based on the value of one referance cell. Example would be: If a1 is adn "X" the the results in cells b1 thru b10 would be green else they would be blue. Is there a way to do this using Excel 97?
Format Color For Range Of Cells Based On Cell Value
I have been tasked with creating a Macro in order to help speed up a rather simple set of steps that are taken to format a spreadsheet each day. The last step, however, is not so simple. I would like to add a statement to the macro code that does that following:
Based on the value of a specific cell, color the cell and all corresponding cells within the same row (from colums A to AA) yellow. Essentially, cells in column D may have a the value "No Allocation", and if they do, I will need to format that cell yellow as well as all the cells within the same row for the colume range A to AA.
Format Cell Color Based On Others Cells Not Being Blank
I know that I need to use Conditional Formatting, but I can't get the formula code correct.
This is what I need it to do:
If cell A2 is populated (not blank) AND cell B2 is blank, then cell B2 to be highlighted in bright red fill.
If cell A2 is populated (not blank) and cell B2 is also populated (not blank), then leave formatting as per normal
If cell A2 is blank then leave formatting as per normal
Basically, as soon as someone types anything into cell A2, cell B2 to turn red. Then once they have input something into cell B2, the red highlight can disappear. The inputs will be text, not numbers, so I can't use </>
I have tried varying circumstances of IFs, ANDs, NOTs, ISBLANKs etc and I can't crack
Change Cell Format Based On Existing Currency Format
I would like to have a single button that changes a range of cells from the USD to EURO to perhaps CAD symbol. Can this operation be performed, such that if I start in dollars, and I click the button once, it shifts by range to EURO (not formulas...just symbol)...and then if I click the same button again, it goes to CAD, and then back to USD with a third click?
Cannot Change Cells Number Format
I cannot change the number format in a single sheet of my spreadsheet. I can go to format cells and select colour etc but it will not change the number format.
Another issue on the same sheet is that linked cells i.e cell b3 formula "=A3" displays "=A3" not the actual value in A3. Again, i cannot change the number format of this cell and yes, auto calc is on.
Whilst not wanting to offer a partial explaination (per the rules) just before this happened i was pressing a combitation of Shift+Control+` - not sure if that is relevant.
I want to get the sheet back to its 'normal' working order.
Scale A Number Using Format Cells Custom?
I have a number in a cell of 1025132.36. I need to scale it down to this number with this formatting: 1,025.13 (Two decimal places with comma separaters)
Basically it is dividing it by 1000 or scaling it by 3 (move the decimal place to the left three places). I cannot use /1000 in the cell and I cannot reference it to/from another cell. I must use something under Format/Cell. I was trying to create a custom format.
I saw in another forum thread to use #”.”###. This does the scaling but then I can’t make the number viewed with only two decimal places. Also when I tried to add comma separators for thousands it put a comma right before the decimal.
Format Cells Based On Their Value
I was wondering if anyone knows how to create a formula that is dependent on the colors of a range of cells subject to conditional formatting. For example, I would like something that could answer =if(A1 is red,"Phillies",if(A1 is blue,"Mets","Marlins)). However, the text color will be generated from conditional formatting, so most of the VBA code I have found thus far isn't working properly. Does anyone know of a solution?
Conditional Format Formula Based On Two Cells.
I want to create a conditional format formula which is based on values in 2 cells.
Cell B1 = 100
Cell C1 = 50
I want a formule for cell A1 which turns red for exmaple when B1 is higher then 85 and Cell C1 is lower then 85. Only in this case the conditional format should work.
Format Cells Based On Conditional Count
I have a drawing register that contains drawing reference numbers in column D. When a new revision of a drawing is issued it is added to the bottom of the list. What I would like to do is any previous revisions to be greyed out and struck through.
The conditional formatting
works well but strike through all duplicates, I need the most up to date left without the format until it is superceeded
Change Cells Format Based On Selection
I have created a named range that includes raw data layed out in 4 columns (Finish Name, Fill Color, Pattern, Font Color). On a separate sheet, when one of the finish names is selected in a particular cell, I want the cells fill, pattern, and font color to change according to the raw data. Below is the vba code I am trying to get to work. However, it is not working. It does not break, but does not achieve the desired result. It looks like the variables are getting set correctly, but still not changing the cell when they are referenced. I am only testing it on the first finish color, as seen in the code. All of the rest are static values for the cell, but I want the values to be dynamic.
ActiveCell = frmFinishSeparator.cboFinish.Value
Set rngTarget = ActiveCell
With Sheets("Pick List Do Not Tamper").Range("Finish_Cell_Color")
lngFinishCellColor = . Find(What:=rngTarget, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 1)..............
Format Cells Based On Values In Pivot Table
I have a pivot table and next to it I added some calculations which are based on data from the table. In my "real" file, the cells next to the PT are formatted with conditional formatting based on other values in the sheet (see attached sample file). What I now need to do is add borders to the cells in columns “D:G” only if the cells in “B5” and downwards are not empty. I have tried doing it with conditional formatting but there are some other rules which contradict this setting.
The idea is to make the cells in columns “D:G” look like they are a natural extension on the PT. However, the table can grow or shrink (in length) and I want to see borders only around cells that are on the same rows as the data in column “B” (staring with B5). I think that whatever solution you can provide (if you can...) should be based a workbook_change event, because in my “real” file, the length of the table is changed whenever I select a different “page” of the PT.
Multiple Conditional Format Conditions Changed Based On Another Cells Value?
A1:A6 have numeric values 1 to 6.
I want the conditional format values to change Based on the value in Cell B2.
If the value in B2 is "old", then I want these conditional format conditions for A1:A6. Numbers between 1-2=green text, 3-4=orange text and 5-6=red text.
IF the value in B2 is "new" then I want the conditional format conditions in A1:A6 to change so 1=black text, 2-3=green text, 4-5=orange text and 6=red text.
I know how to do multiple conditional formats but i have no clue how to change multiple conditions based on another cells value.
Automatically Format Range Based On Formula Result Of Cells
The following code works perfect but the "change" event is only triggered when working directly on intersect range. Tried using the "calculation" event but could not figure it out. This is what I want:
1) To replace the code provided below using the calculation event
2) To only trigger the event for the row(s) where the new value was generated, not for the whole "For Each" statement
3) To use one single code for all worksheets, instead of copying the code in every working worksheet on the workbook, if feasible
4) And I would like a "second alternative", where the user of the workbook can click on a button and trigger the event on every row on the workbook that has a non empty cell within the intersect range, assuming that the intersect range column is the same for all worksheets
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, fc As Long, bc As Long, bf As Boolean
Set d = Intersect(Range("I:I"), Target)
If d Is Nothing Then Exit Sub
For Each c In d
Select Case UCase(c)
fc = 1: fb = True: bc = 4
fc = 2: fb = True: bc = 3
fc = 2: fb = True: bc = 46
fc = 2: fb = True: bc = 5..................
Unable To Format Number Within Cell
Working on matching 2 column of numbers.
Unable to match as 1 column has format where number has a space in front of first character.
_425638 as opposed to
Not an underscore tough
Have tried text to columns - have tried editing cell and replacing space with nothing via edit/replace but no joy only way appears to be entering cell and deleting manually - there are thousands to update.
Number Custom Format A Cell
is it possible to custom format a cell in order to:
a) Present 3.33333 as 3.33 (max. of 2 decimal digits or less)
b) Present 3.333 as 3.33
c) Present 3.33 as 3.33
d) Present 3.3 as 3.3
e) Present 3 as 3 (NO zeros and NO decimal dot)
Subscript Macro: Go Cell By Cell And Format The Last Number
Is it possible to make a macro that will go cell by cell and format the last number of the cell as subscript? Here's an example:
Brooks #1H 5Stewart #8H, #9H 8Stevenson D Unit #1H 2150,000 BblsStewart #8H, #9H 8Stevenson D Unit #1H 2Brooks #1H 5Stewart #8H, #9H 8Holasek #1H 5Brooks #1H 5Stewart #6H, #7H 8140,000 BblsRayburn Unit #5H, #6H, #12H 6160,000 BblsHolasek #1H 5450,000 BblsStewart #6H, #7H 8#N/ARayburn Unit #5H, #6H, #12H 6Stewart #6H, #7H 8Holasek #1H 5Rayburn Unit #5H, #6H, #12H 6Stewart #6H, #7H 8Holasek #1H 5
I would like ONLY the last NUMBER of the cell to be formatted as subscript. The cells with "Bbls" would be left as is.
Cell Number Format And Display - 2003
I enter in numbers, eg: 1, 2, 10, 24, 100, 1000 in a consecqutive cell range
They display as: 0.01, 0.02, 0.1, 0.24, 1, 10. Default cell format is general. If I change cell format to Number (with default display of 2 decimal points) it displays: 0.01, 0.02, 0.10, 0.24, 1.00, 10.00. If I also specify no decimal points, it displays: 0,0,0,0,1,10
The only way I can get the numbers I enter to display (and store) as 1,2,10,24, 100, 1000 is to change cell format to text and THEN retype th numbers into the cells. But, then it creates the green error comment in top left of each cell.
Format Number & Text In Same Cell
i have the next formula
=IF(C6="PRODUCT 1","$"&VLOOKUP(B6,N:T,3,FALSE)&" & "&"$"&VLOOKUP(B6,N:T,5,FALSE),"N/A”)
which aberrantly returns text:
$24.675 & $26.2
I was wandering is there a way to format or put a formula which will format the numbers to be with two decimal places. The cell containing the formula is already formatted as number and the source tables are numbers with two decimal places.
i want the result to look like this
$24.68 & $26.20
Cell Format For Partial Repeating Number
I have an excel sheet that we need to type in an account number it always starts with 8774100 then has 9 more numbers. If I leave it on number formating it always changes the last number to 0 so I format the cell as TEXT. Anyway is there a way to get the 8774100 to automatically go in and only have to type the last 9 numbers? I know I can copy paste but then I have to double click or make sure I enter the last 9 numbers at the end or use the top entry. The data entry people just want to hit enter and go from line to line fast. In the past I just divided the cell into 2 cells and made the 8774100 in column A and columb B was the ending 9 numbers. But they need to be in the same cell.
Change Result Cell Format Based On Input Cell Value
Macro Based on an input cell’s (e.g. B12) setting of “yes” or “no”, format several other cells (e.g. B20:G20, B23:F23, C26:J26) as follows: keep the existing yellow shading if input cell is “yes”, change to grey shading if input cell is “no”. I would like this macro to run automatically whenever the input cell value is changed.
Preserve Cell Number Format In Message Box
I'm writting an Excell VB application which reads data from my spreadsheet and places it into the text member of an IXMLDOMElement node in order to generate a QBXML request.
The cell I am reading from has datatype number with two decimal places as required by the xml scheme. The problem is that the Range.Value Variant data type drops the decimal point and any trailing zeros so that 1197.00 become 1197. Unfortunately, the QBXML parser requires to see the decimal as well as trailing zeros. So, 1197.90 must show up as precisely that. Changing 1197.90 into 1197.9, for example, also breaks the xml.
My Excel VBA book specifically states that a Variant data type holding numerical data will maintain the data type assigned to it.
I also tried declaring a local decimal (Dim MyDecimal As Decimal) thinking to try copying the Range.Value value to it before assigning to the QBXML field in order to force the correct data format but my VBA macro compiler does not apparently recognize "Decimal" as a valid data type event though my book clearly indicates it is one of the valid VB data types.
I am using Excel 2000. Are these problems solved in newer versions?
Type A Number In A Blank Cell With The General Format
My excel recently the General number format so that when the general number fomat is used and I type a number in a blank cell with the general format, the number is always divided by 10.
E.g., I enter "102", the number is automatically improperly converted to 1.02.
However, if I enter "=102", the number is properly entered as 102.
How can I reset the General format to the original setting without this divide by 100 problem?
Retrieving A Number Format From A Cell Grouped With Letters
i need to find a way to search for numbers in a cell that are attached at the end of a group of letters. ex. (xxxxxxxxx01-01-001). i want to search backwards in the cell going right to left. what i need to do is once i find the numbers i need to go to the last number ex. (......x01-.....) and in front of it place a space ex. (......x 01-.......). right now i havent come up with a formula that can do this.
Pass Number From TextBox To Cell In Numeric Format
I have uploaded a copy of the spreadsheet I am working on and have completed the user forms and coding for march the 1st only, so if you test, please use the options march and then the 1st!
The problems i have is, when I enter a number into a text box and press next page, the numbers fill where i want them to but the cells do not recognise them as numbers, therefore conditional formatting doesnt work! Even if I change the cell properties to numbers, this does not remove the error!
Second problem!! If a user forgetts to enter a value in a box, or they wish to edit just one value, and go back into the user form to change a figure, when they press next page, all the values in the column seem to disappear!
Conditionallty Format A Cell Based On Another Cell's Value
Is it possible to conditionally format a cell based on the value of another cell?
I am trying to organise historic data of a horse racing system I am working on, and I have a column of finishing positions. Obviously if the position is 1, then the horse was a winner, and if the value is over 1, the horse lost.
I'm not sure where I am going with this at the moment, but if I can conditionally format a cell based on the value of another cell it will give me a starting point.
Return A Value Based On Cell Format
I don't even know if this is possible but can you use some formula within Excel to return a value to a cell based on the cell formatting of another cell? Here is specifically what I am trying to do:
Cell A2 has a value of 10. If the font color of that cell is black (or "automatic") I want a formula in cell A1 that will return a value of "+10". If the font color of cell A2 is red, I want the formula in cell A1 to return a value of "-10".
Numeric Format Based On Cell Value
I am stuck on this one. In the attachment I have values pulling into N3:N7 based on a drop down choice. Some of the numbers are numeric (no decimals), others are currency (no decimals), others are percentages (2 decimals), etc.
Is there a way to pull values I'm currently pulling into column N and have those values match the format of the source? I've tried converting it into TEXT but then I cannot place conditional formatting data bars on the values and have it work.
Copy Based On Cell Format
Is there a macro that can copy the information of any italicize item in Sheet1 to Sheet2 and automatically italicize the item in Sheet3 if the same item exist?
In my attached example:
In Sheet1, B3 to B8 contain information about a restaurant.
I want the macro to copy any restaurant (and its information) that is italicize to Sheet2. And in Sheet3, any restaurant that is in italic in Sheet2 will become italicize in Sheet3 if the name is match. Is there such a macro that can do these things?
Copy Format Of Cell Based On Value Of Another
I have been working several weeks on producing a macro that can quickly eliminate blank lines in an .xls file which is created by exporting a Crystal Report into Excel. The macro can do that, but the big problem is that it freezes up the users PC for 3-5 minutes. If you go into Task Manager you can observe that CPU usage is at 100%. The .xls files that I am trying to run this macro against are around 50,000 rows long with data in about 10 columns. About 2/3 of those rows are blank. This is an inherent problem when exporting Crystal Reports into Excel. I went to Business Objects website (Crystal Reports) and their solution is simply not an option. They suggest removing If-Then-Else logic. I simply cannot do that because that is the heart of how the report selects data for each row to print in the report.
Below is the code that I wrote with documentation to explain how it works. If you would like to try it I attached an .xls file. It is only 800 rows long so that I could stay under the forum's requirement of attaching nothing larger than a 48.8 kb file. The file has only one column, 800 rows long. You may want to copy and paste it a few times to make it longer. When running the macro it will open a dialog box so that you can go wherever you copied the file, and open it up.
Dim fileToOpen As String
Dim lRows As Long
'open dialog box for user to access an Excel file for deleting blank lines
fileToOpen = Application. GetOpenFilename(FileFilter:="microsoft excel files (*.xls), *.xls", Title:="Select an .xls file to open for deleting blank lines...", MultiSelect:=False)
If fileToOpen <> "" Then
Conditional Format Cell Based On Value Of Another
I'm afraid as its a multiple conditional format and thus needs coding. At the moment I'm using the following
Private Sub Worksheet_Change(ByVal Target As Range)
' Conditional Formatting for more than 3 conditions
Dim rng As Range
' Target is a range::therefore,it can be more than one cell
' For example,,someone could delete the contents of a range,
' or someone could enter an array..
Set rng = Intersect(Target, Range("E27:E34,G27:G34,I27:I34,K27:K34,M27:M34,O27:O34,Q27:Q34,S27:S34,U27:U34,W27:W34,Y27:Y34,AA27:AA34"))
If rng Is Nothing Then
This works very nicely, so that for example if I type the number 10 into cell M33, that cells fill colour becomes black (and the text turns white). Alas I would now also like to extend the fill and text colour to the cell one to the left too. To take this example I would type 10 into cell M33 and cells L33 and M33 would both be filled black and their text would be white. I'm not sure if this requires a change to the code, or if its possible to just have a formula or something which copies the fomatting of one cell into another
Format Border Based On Cell Content
I have a dynamic table (rows regularly added or deleted) that is generated automatically based on choices made by the user in another worksheet. I need a function that will examine all the cells in one column, and when it finds a difference between two cell entries, it will change the border between these two entries to dark (medium weight), and then extend this border across the table. The end result should be that wherever the cell contents differ within a column, the table is divided by a darker border across the entire row.
Conditional Format Cell Based On AND/OR Condition Of Another
i m trying to format a cell so that it colours in red when a specific text is chosen in another cell, ive tried conditional formatting but I cannot get it to work. e.g column A has a drop down list, if GB or IEN is chosen in that cell then cell in column K will become red. (as if these two are chosen then they have to input a number into column K). Once they have inputted the number into column K i want it to go back to its original format. Or if they dont select GB or IEN then it would stay in its original format aswell.