Conditional Number Format Based On The Value Of A Specific Cell
May 9, 2009
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.
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue If AI3=D3 & D3 is red, format AI3 blue Otherwise, leave AI3 unformatted.
With the click of a toolbar button, I've got my vba inserting a new tab, renaming it, inserting headers and now working on the code to insert all the formulas needed in 12 columns to copy down as far as there is data.
The 4th column D is the hardest. Currently, I'm using manual "Conditional Formatting" to colorize the cells in Col D but need the VBA to place that "Conditional Formatting" code into the sheet so that when the sheet is created, it will hold the conditional formatting feature.
Specifically, the formulas I'm using in the Conditional format wizard are:
Formula: =$B2$D2 then turn the cell red Applies to: =$D$2:D
Copy down (in other words, the colorizing should work all the way down Column D as long as there is data in Col D.
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 Exit Sub Else.....................
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
Is it possible to format a cell based upon the format of another cell? For instance, if the cell to the left of a target cell is bold, is there a conditional formatting cell that can be used so that the target cell is bold as well?
I want to highlight an entire row based on the contents of of a particular cell. For instance, if c4 contains data, highlight the entire row 4. If c5 is blank, do not highlight the entire row 5...and on and on...
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.
I'm trying to make a schedule for some of my employees (300 to be exact) and I was trying to work out a way of speeding things up a bit. I've made plenty of pretty complex equations for working with the task but the solution for what I would like to do now alludes me.
Here's the challenge: I have a sheet where I would like to insert employees within row 4 and then have their shifts shown visually in the column below that, and have the hours they work put into a merged cell (or something similar), like what can be seen in the blue cell under "Employee 1" (C6). What I would like to do is just have something like a VBA detect that a series of cells is merged (red cells) and insert the corresponding time, located within B5:B33, into that cell (with same format as the blue cell). Also, above the employee's name I could put a button to run something like a macro to run the program for the corresponding employee directly under that button.
Essentially I want to merge a series of cells, hit a button, and have excel locate and input the correct times within that merged cell.
I am looking for VB code that will format a range of cells from a number format to a percentage format based on a reference cell. The reference cell is a drop down created from a list validation.
For example: If the reference cell (cell A1) =1 then cell range B2:C4 would be a number format. If A1 = 2 then cell range B2:C4 would be a percentage format.
I have searched for hours trying to find a relevant thread; I know they are out there, maybe I just wasn't typing in the correct search words.
Not very good at this but I have the basic sheet attached. All I want is a way to highlight the first cell when the date is within 30 days of the "Due date".
I am working with the Conditional Formatting, which is fine for one cell. Here is what I am trying to do: IF cell in $A1 = 1 then bold $B2:$M2 and apply solid line border to top of cell ranges. I have tried conditional formatting but it only formats the cells in column A. And I can't seem to find a BOLD statement for the cell formulas.
My cell has a 'P' or an 'F' plus some additional text. I'm trying to change cell color based on the 'P' or 'F' but I can't get it to recognize the letters. Samples of what I've tried: =IF(LEFT(L23,1)=P) =IF(MID(L23,1,1)=P) etc.
I can 'FIND' the 'P' or 'F' but there may be a P or F else where in the cell so the color may come up incorrect. I'm trying to get it to just look at the first character in the cell. Can the 'IF' be used with CFs?
I need to make an entire column formatless if the cell in row 3 contains no text. I want the whole column to just appear like a normal formatless column, but if there is text in the row 3 cell, I want it to be the way I have it formatted right now.
conditional format formula that is based on a text range within a cell. In my case, I would like to compare the low and high range when inputted as a number followed by the quotation marks followed by space then hyphen, space then number and ending in quotation marks. The quotation mark is being used to represent inches.
Like this in cell D14: 0.2” - 2.2”
I am using the following condition format formula which works for the strict case above.
HTML =OR(D16<LEFT($D$14,3)+0,D16>MID($D$14,8,3)+0) However, there will be times when users using this spreadsheet may leave out the spaces on either side of the hyphen, or add more than one space between the number and hyphen. Also if additional digits are added, my formula above does not include all the digits in the results. I prefer to leave the quotation marks in...........
I'm racking my brains as to how I can structure a formula to conditionally rank a value in an array against only those values in the array whose corresponding criteria cell includes a specific letter.
So for example I have a list of 12 values, say 126; 239; 0; 171; 162; 157; 130; 199; 122; 153; 0; 15. Each of those values corresponds to a heading, say: CDE; DFE; FGE; DFE; ERD; DEA; BDF; DFB; CDE; CEF; CAB; FAB. As you will note some of the headings may or may not be the same and may or may not include the same letters in different orders.
How can I write a formula that ranks in ascending order a given value drawn from the above list (which will be in another cell but which in this case is, let's say, the first value: 126) only against those values whose heading includes a specific character, for example the character C (the character in question will vary and be defined in a specific cell).
As an added complication I need the ranking calculation to exclude any zero values. So in the above example what the formula needs to do is rank the value 126 against a sub-set of the whole array comprising only the values 126; 122; 153; 0.
The answer I need is 2 because, discounting the zero value, 126 is the second highest value.
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 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'll get straight to the point: How can I change this text format from 474556788 to 0-47455678-8 (dash added after 0 and before the last number). I need to apply this to around 5000 rows (can't do that manualy). I want it to be int his format:
474556788 to 047-455678-8
Tks Auto Merged Post;474556788 to 047-455678-8 (This is the wanted format)
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
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.
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.
