A1=text A2 will either be "yes" or "No"
What I want to do is format A1 by either a fill colour or text colour. Any type of format I guess. If A2 is "Yes" then A1 is green and If A2 is "No" then A1 is red.
I have configured conditional formatting to check valid entries in a dependant list. I used the solution provided by SHG at:
http://www.excelforum.com/excel-prog...ant-lists.html
Now I need to use a macro to activate and deactivate the conditional formatting due to performance issues.
When the formula for data validation includes the indirect funtion I got a compile error and the macro won't run.
This is the most simplest conditional formatting which is working except on some cells. I want to compare two columns cells ex : if A=B , then format A as green.
Its working, the "formula" and "cell value contains" everything except for few cells. Cell B has formula's in it related to other cells. When i enter values in other cells, Cell B computes but A refuses to format "green".
If I just enter same value[ex 1, 1]in both the cells sans formula ,it highlights A green . I want to know , if the formula in cell B is causing the errors in formatting and if it is
I downloaded the checkbook register from Microsoft.com and am trying to add a
few cells at the end to quickly calculate my outstanding debits and credits
so that I can balance my statements automatically. I figured out how to write
the formula properly (eventually), but the cells will not take on the
currency format no matter what I do. I have tried pasting the format from the
other cells within the register, tried clearing the format and reapplying the
currency format, tried accounting, etc.
I have a cell that has a conditional formatting of Cell Value< 90 the cell value is based off a formula which is
=LEFT(E48,LEN(E48)-1)
which when it comes out to me less then 90 I need it to color red. But it does not is there something that I need in the conditional formatting to tell that its a number?
How to customize a format or a formula in a cell in order to :
multiply by 2 any edited value in the cell .
Ex: by entering the nbr 3 and clicking enter the cell will result nbr 6 .
Is it possible to apply formatting to a formula in a cell when you are combining that formula with text? As an example, I want to format the following as a percentage: ="The result is"&" "&(a2/a1)
Currently, it is returning [e.g.] ...result is 0.5, instead of ...result is 50%
I'm referring to a post I tried to solve:http://www.excelforum.com/excel-gene...ml#post2227328
NO need to read. Try the attachement instead.
Steps done:
Select Column D
CF function =D1<A1
Select Column A
CF function =A1<D1
If I select cell D3 and look at the CF then I see:
CF: =D1<A1
where I would like to see
CF: =D3<A3
I remember this was the case in <=XL 2003? (now using 2010 beta)
Is there a Countif that works on the criteria of the font being 'Red' or 'Bold' ? This needs to work based on conditional formatting I have on the cells in question.
View 7 Replies View RelatedI have a large spreadsheet with a number of columns that are set up for conditional formatting. For example a column of "BUY"'s and "SELL"'s where the "BUY"s are formatted with the default "light green fill, dark green text". I have other columns that are conditionally formatted using a formula. When I base the result on a formula I don't seem to have the option to use the default formats. Is it possible to use the defaults or maybe even how to customize my format to look like the default? It would look a lot better if I had a uniform red/green style throughout the sheet.
View 1 Replies View RelatedSay I have the following....
12345678910
59465465495652505853
I conditional format the top number which in this case is 65, how do I highlight the number directly above it? Either by another conditional format or some formula that picks the number out. I've tried HLOOKUP and VLOOKUP, but its giving me spurious results.
I want to set a conditional format to cell A1 with a value in cell P1 When the value in P1 is between 0 and 10 the conditional value in P1 should be set.
Note
cell A1 is also set with a conditional format to put a border around it when there's a value in A1 (cellvalue is not equal to " ")
So the formula referencing P1 would be a second condition.
I have this fairly simple formula which decides whether to shade a cell or not
=AND($X$1<>"TBD",R3<>"None",AC3="Y")
This is set in cell R3 and I want to copy it all the way down the cells in the R column. However, when I copy & paste (and copy and paste using paste special, formatting) the R3 and AC3 cell references do not update to match their relevant rows. eg If I highlight cell R26 the conditonal formatting formula still refers to cell R3 and AC3, not R26 & AC26. I'm using Excel 2010 but I don't recall this happening in 2003.
I have a file where the cells are all formulas.
Employees are able to key in a number instead of using the number derived from the formula.
I want the cell to use conditional formatting without the use of macros, so it will highlight yellow if the cell is keyed in instead of a formula.
Is this possible? I have tried numerous things, but none seem to work.
Example:
Cell A6 has the number 24,248 derived from a formula.
Employee thinks the number should be 30,000 and keys that number in.
If it is 24,248, I want the number to have no fill, but if it is a keyed in number, I want it to be highlighted.
Here is what I am trying to do in lay terms:
If A2 and B2 on Sheet("Master") are not highlighted, copy and paste them into Cell A3 and B3 on Sheet("Job List - Numeric"). Do this up to Row 50 on Sheet("Job List - Numeric"). Once Row 50 is filled, jump to pasting to D3 and E3 on Sheet("Job List - Numeric") and continue up to Row 50 and so on....
I need to do this on one sheet in numerical order based upon Column A and another sheet in alphabetical order based upon Column B.
Here is what I have in code so far:
Sub Final()
Application.ScreenUpdating = False
Dim c As Integer
Dim x As Integer
For Row = 2 To 145
Range(ActiveCell, Cells(ActiveCell.Row, ActiveCell.Column + 1)).Select
[Code] .......
how to apply conditional formatting via VBA to a range of cells based on input from another range of cells. Obviously this would be easy in Excel 2010, but I'm still using 2003 at the office and it needs to stay in this format to be readable by other users:
For cells M8:EK8, my conditional formatting
condition 2: Formula Is =AND($E$8>=M2,(($E$8-$D$8)>=(N2-$M$2))), color index is 40
condition 3: Formula Is =AND($F$8<=M2,$G$8>=M2), color index is 39
I want to add:
condition 4: Formula Is =AND($H$8<=M2,$I$8>=M2), color index is 40
condition 5: Formula Is =AND($J$8<=M2,$K$8>=M2), color index is 39
and so on
The cells in the range M8:EK8 are blank, they only get colored based on input added to D8 to K8. If there is no input, then the cells should be uncolored.
resource tracking ex.jpg
I am trying to apply icon conditional formatting in a cell. The cell contains the following formula: =VLOOKUP(D20,'owssvr(1)'!O:W,9,FALSE The formula results in a "2", "1", "0" or "-1" in the cell. The icon conditional formatting is not working at all (no icon appears). I have the conditional formatting setup as numbers Green 2, Yellow 1,0 etc based on value. If I delete the formula and just type in any of those numbers directly, it works. I have changed my cells to "number" and it still does not work.
View 3 Replies View RelatedI'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.
I am trying to write a macro where I divide the value of one cell by that of a second cell, multiply it by 500 and then to paste the outcome of the formula to a third cell. This is what I wrote
View 5 Replies View RelatedVba Radian Function. here's an example of my code that produces an error:
myrad = Radians(45)
How to write a VB macro to copy an existing formula from cell A1 to cell A2 and back again.
I want to have a macro button which will act as a toggle each time the button is clicked.
For example the data will either be in cell A1 or A2.
When the button is clicked once, I would like the macro to execute and determine whether the formula is contained in A1 or A2.
If it finds data in cell A1, I want the Macro to cut and paste it to cell A2.
On the second click of the button I want it to do the opposite. Effectively it's a toggle switch.
Formula to cell with macro not working
I'm trying to enter this formula into cell in VBA macro.
I have this running and Section A is working but Section B is not. I basically want it so that if conditions a, b, and c occur, excel pops up with that messagebox.
Condition A: Cells(8, 3) changes via formula referencing an outside cell
Condition B: The new value of Cells(8,3) is a numeric value.
Condition C: The change in it's value is not more than a given threshold
change as defined in Cells(8, 13)
why the second section is not popping up with a msgbox when the value of Cells(8, 3) changes.
Public bbb as long
Sub Other()
bbb = Cells(8, 3)
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim bb As Integer
Dim cc As Integer
[code]....
I want to run a macro when a cell in reaches a certain value.
I have tried the following code but neither work
Private Sub Worksheet_Calculate()
If Sheets("Report Form").Range("I11") > 80 Then Call JobIs80percent
End Sub
Private Sub Worksheet_Calucate(ByVal Target As Range)
If Target.Address = Sheets("Report Form").Range("I11") And Target.Value > 80 Then
Call JobIs80percent
End If
End Sub
I have created a visual schedule for my team using Excel (2003, SP3 if that matters). Essentially, the user puts in pre determined 1-3 character codes in individual cells, and the macros I have act like a complex Conditional Formatting to keep the formatting neat and consistent throughout the sheet. The actual values are inputted directly into the cells though (this is pertinent to my question), and are things like "A" "M" "$" and "TR"
I have a sheet for every day in a week. Since there are multiple team members on any given day, I have recently made another sheet which pulls a single Team Member's schedule Sunday through Saturday and displays their schedule for the entire week. I have used formulas (specifically VLOOKUP) to do this.
The problem that I am having though, is that the macros that I made to format the days of the week sheets, do not seem to recognize the result of the formulas in the individual Team Member's sheet, and thus do not format them as desired.
My macros are written to evaluate a cell's value via [ Range("example").value ] and will act upon it accordingly with more code. I am assuming that a [ Range("example").value ] would see a cells value as the text of the inputted formula, and not the result of that formula. Is there any way around this? or do I need to avoid the formulas all together and write in code to just copy over what I need?
(I hope this makes sense)
I am trying to make a very simple macro, that when it is run, it inserts a formula into the selected cell. But I am having problems with quotation marks etc... As it doesn't understand the 3rd and beyond quotation marks.
Sub Macro()
ActiveCell.FormulaR1C1 = "=IF(A1<3000,"Small", "Large")"
End Sub
I'm sure there may is a way of coding it in VBA properly, but I plan to use this method for a range of basic formulas. The idea is this is saved in the Personal Macro Book so I can start a column of formulas by running a Macro rather than typing it in (the example above is a simplified formula, the actual one is fairly long) - then it can just be dragged down on every Workbook I need it in.
I currently have the following code to copy and transpose data from one worksheet to another:
Range("E3:E6,E10:E53").Select
Selection.Copy
Sheet3.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Transpose:=True
What I would like to do is add to this code. I would like to add the following formula to the cell at the end of that pasted row. =VLOOKUP(Range,LeaveIndicator,6,FALSE). In this case the Range needs to be Row D and the last row of sheet3.
I have written a macro that creates a new spreadsheet and copies text into it. The number of rows varies depending on the input.
Yet, running the macro results in a sheet with the max rows (65k+). This is unmangeable, as it makes it a huge file and size and harder to print.
I only want there to be the number of rows that have text in them.
The larger problem is how these were even created in the first place. Currently:
' Formula for Job Name
Range("A2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[12],8)"
' Fill Job Name
Range("A2").Select
Selection.AutoFill Destination:=Range("A2", Cells(Rows.Count, "A")), Type:=xlFillDefault
Is there a way to incorporate these 3 boxes into one? So if I enter a figure into any cell, it will calulate it for the other two? Without overwriting the existing formula. I have attached the problem.
problem.xlsx
I want to write a formula that inserts a column and then does a vlookup in that new column using the cell just to the left of it as the first variable. The problem is, this could be in any random column so I can't do a static reference to $A2 as an example. So, my question is...
Instead of using =vlookup($A2,Sheet1A1:B50,2,false)
How do I replace the $A2 part with some code that says, "the cell to the left of here..."