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 5 RepliesI am looking for a method to conditionally format a variable number of cells below a single cell into which data is entered.
For example ABC = 6, DEF = 12. Therefore, if I enter ABC into D1 then D1:D6 should be conditionally formatted.
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?
View 5 Replies View RelatedI 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
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:
I'm trying to insert a random number into the unprotected cells on a worksheet depending on the type of number formatting in the cell. There are 4 basic cases:
Number formatted as percentNumber Formated general with no decimal placesNumber formatted general with two decimal placesNumber formatted in thousands
The code below only contains three criteria, I was trying to get that to work first....Based on this criteria I want to insert a random number of an appropriate size. Please find the vba below.
Sum randomnbr()
Dim F as String
For Each c In ActiveSheet.Range("A1:AK2500")
F = c.numberformat
If c.Locked = False And IsNumeric(c.Value) = True And c.EntireRow.Hidden = False And c.EntireColumn.Hidden = False Then
If F Like "*#,##0*" Then
c.Value = Round(Rnd, 0) * 1000000
If F Like "*%*" Then
c.Value = Round(Rnd, 2)
Else: c.Value = Round(Rnd, 2) * 1000
End If
End Sub
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:
=IF($C4="F/T",SUM($AJ4)+($AK4/2),SUM($D4:$AH4))
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.
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
Etc...
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.
I have been trying to format the rows on this sheet to color scale red based on the number of repeat text strings in Column E. Referring to the attached example sheet, '321/312.2/321.3' appears the most times and the goal is to color code the rows it appears in the deepest shade of red, then the next highest occurring string would shade the rows it appears in a lighter shade, etc, in descending order. Our team currently does this across multiple sheets manually everyday and it would be a real time saver if we could get excel to do this automatically.
View 14 Replies View RelatedIf F2 is blank and E2 date is older than or equal to today, bold and color E2 RED.
View 2 Replies View RelatedI 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?
View 3 Replies View RelatedI 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.
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
I would like to format the color of cell A1 on Sheet 1 based on true or false values from cell range A1:A10 on sheet 2. For instance:
1. If all cells on sheet 2 in range A1:A10 were false then cell A1 on sheet 1 would be red.
2. If some cells on sheet 2 in the range A1:A10 were false and some were true then cell A1 on sheet 1 would be yellow.
3. If all cells on sheet 2 in range A1:A10 were true then cell A1 on sheet 1 would be green.
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
Text to Number or General.xlsx
The included, small database is formatted as text. It is a text feed from an outside source. I simply want to format the cells into either numbers or general format but not text... seems simple, and it should be, but the only way I can get this done is to go to each cell and access the formula bar and re-enter the number by pressing Enter.
I am working on a macro and this section has me stumped. The worksheet columns are A:AE. Occasionally, the procedure needs to select a varying number of cells in a row which don't always fall in the same column, count the number of cells in the selection and then paste the data on the row above it, in column ("AE" minus the Selection Count).
Here is what I have so far:
[Code] .....
The red-bold bracketed code is the syntax I'm not able to figure out how to phrase.
So I have this list (I made it a little bit shorter).
So what you see is two different tasks (01 and 02) and three different conditions (A, B and C). In column B you see the result I would like to have. '/Searchtask_01.html' in A1 belongs to conditions A, because it is in session A. However, '/Searchtask_01.html' in A10 belongs to conditions B, because it is in session B.
How to get the results in B with a formula?
how do you place 100/0 so the /0 stays static and the 100 can be used to operate? (ie: 100/00, 101/00, etc)
View 5 Replies View RelatedHere’s an example of my data:
A B
1110AAAAAAA
2220BBBBBBBB
3330CCCCCCCC
4330DDDDDDD
5330EEEEEEEEE
6440FFFFFFFFF
7440GGGGGGG
8550HHHHHHH
I need your help to figure out how to merge the above data to look like this in new blank worksheet using a VBA macro:
A B
1110AAAAAAA
2220BBBBBBBB
3330CCCCCCCC
DDDDDDD
EEEEEEEE
4440FFFFFFFFF
GGGGGGG
5550HHHHHHH
I have data in cell A1 that often changes its number format(from $ to %), can i have the number format change in cell B1 whenever the number format in cell A1 changes?
View 3 Replies View RelatedI 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 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?
View 5 Replies View RelatedPlease see attached sample worksheet. Column A will be generated by the user manually.
I'm looking for a way in VBA to have A1:D20 in Sheet2 copied and pasted in the "Bank Reconciliation" Sheet based on how many "Markets" there are in Column A. Then, once that's complete to have A22:D30 (the smaller box in Sheet2) copied and pasted directly below those results.
I have what the macro would hopefully generate to the right in "Bank Reconciliation" (B6:E54) as an example. So if there's a market in A1, copy and paste the box to B6. If there's a market in A2, copy and paste the box directly below the first (B26) etc. etc. until it's done, then paste the smaller box directly below whatever the macro generates.
Book2.xlsx‎
I'm looking to conditional format a cell/cells based on whether a formula result returns a whole number or not.
I don't want to include the formula in the sheet itself, just have that as the formula in the condition.
The formula will be along the lines of:
=IF(SUM(BB10/BA10)"a whole number",TRUE,FALSE)
My problem is, is that I don't know who to refer to "a whole number" in Excel formula language.
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.
I 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 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.
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?
View 4 Replies View RelatedFormat Random Number Cells As Draw Result Entered ...
View 9 Replies View Related