Conditionally Formatted Cells Which Turn The Text In Them To Red If The Value Is Less Than 40
Aug 18, 2009
I have a series of conditionally formatted cells which turn the text in them to red if the value is less than 40. This works fine.
Occasionally however a value of less than 40 will need to be entered along with the letter 'v'.
ie. 39v
I'd still like this to be coloured red, but it's obviously coming out as black.
Is there a way to sort this out?
edited to add : Im actually using a separate cell to enter the value 40 (as the value can change).
View 7 Replies
ADVERTISEMENT
May 1, 2014
I am looking to produce a Macro to select conditionally formatted cell's from a worksheet, i got as far as selecting those cell's but i need it to only select cells that have been filled.
View 6 Replies
View Related
Aug 5, 2014
spread sheet that I need to do for work. Unfortunately I do not have excel on my home computer to be able to attach a spread sheet
The spreadsheet has one column (A) with a list of questions. The column next to this (B) has either a red or yellow cell in each row (a red cell would be a high risk to the business if the answer to the question in that row was no, and a yellow cell indicates a moderate risk to the business in the answer to the question in the row was no).
The third column (C) is conditionally formatted so if a 'y' was placed in any of the cells they would turn green. If 'n' is placed in any of the cells the cell would change to either red or yellow (this would depend on what the colour was in column B).
I need to know a formula to count cells by colour. So the number of red, yellow and green cells in column C would be counted automatically into a totals box for each colour at the bottom of the spreadsheet. I have tried some online suggestions but couldn't get these to work for cells that had been conditionally formatted. I'm not the most experienced person with spreadsheets (this time last week I couldn't add two cells together)
The final thing I need from the spreadsheet (and I'm not even sure if this is possible) is for a total box to be colour co-ordinated based on the number of red, yellow and green cells in column C. I would need the total box to be green if all column c is green, yellow if three or less cells in column C are yellow and red if any of the cells in column C are red or there are more than 3 yellow cells in column C.
View 2 Replies
View Related
Dec 10, 2007
I have columns that have different colors, depending on there conditional format i.e. Green or Red. I want to count the number of either Red or Green in a total at the bottom. I have search and found a few answers
Function CountColor(rColor As Range, rSumRange As Range)
Dim rCell As Range
Dim iCol As Integer
Dim vResult
iCol = rColor.Interior.ColorIndex
For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = vResult + 1
End If
Next rCell
CountColor = vResult
End Function
BUT these don't seem to work on conditionally formatted columns
View 4 Replies
View Related
May 3, 2009
This is my first use of the forum as I only joined yesterday after a recommendation from another colleague.
I am attempting to write a macro to clear the contents of cells in a range that have conditional formatting. Below is the formula I have used...
Sub clr()
Dim r As Range
For Each r In Range("09:050")
If InStr(r.Interior.ColorIndex, "40") Then r.ClearContents
Next r
End Sub
While the formula runs without error, it does not clear the contents, although I read on another thread on this forum that you cannot clear contents of cells that are conditionally formatted.
Alternatively, if there is a more efficient way to clear cell contents (without using a formula in the cell itself),
View 9 Replies
View Related
Oct 22, 2013
pivot table?
Is there a Color Function type vba code that can give me the color integer? I have one that can do that for cells that I've manually colored but I can't find a code that works on conditionally formatted cells.
View 1 Replies
View Related
May 30, 2014
I have a spreadsheet where i do need to count cells values that are formatted as text but i do need to ignore the real text in some cells, in this formula i do need to add values depending of the adjacent cell.
I have attached a example : New Microsoft Excel Worksheet.xlsx‎
View 8 Replies
View Related
Nov 14, 2012
Is it possible to keep ending zeros in cells that are formatted for Text or General?
I have a column with numbers like the following: 264400
I need to format this number for three decimals so it will look like the following: 264.400
I need the column to be formatted for either Text or General. Currency breaks a system.
View 1 Replies
View Related
Aug 31, 2012
I have a range of cells that change colors with conditional formats based on the cell value from high to low. I would like to link the cell color to an object such as a circle or rectangle. When the cell value changes along with the conditional format, the color of the object will also change.
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
Jun 30, 2009
Using 2007, I need to conditionally format a cell colour based on 5 other cell colours that have been conditionally formatted. The 5 other cells will be coloured either red or greem. What I want to do is have an overall status cell that would be show green if all of the other 5 cells were green, amber if 4 of the other cells were green and one was red and red if 3 or more cells are red.
View 4 Replies
View Related
Mar 25, 2012
I have recently found that when I copy ranges (usually containing formulas), I only have the option of copying these ranges as text ( or vales). I can no longer copy formulas , (or formats, col width etc) in my excel worksheets.
This problem has only recently occurred and applies whether i have one or many spreadsheets open.
View 6 Replies
View Related
Mar 30, 2012
I have written below code
Sheet1.Cells(1,2)=Shett2.Cells(2,1)
and my sheet1 cell has a value which is bolded and after running above code the sheet2 cell is displaying unbolded value
how to copy the format as well
View 4 Replies
View Related
Jan 10, 2007
Is it possible to sum cells that contain numbers converted to text? I used TEXT function to convert them because I needed to format them (to display three decimal digits, if number < than 0.05, three digits otherwise). But now SUM Formula doesn't work.
View 4 Replies
View Related
Feb 21, 2014
Is there a formula I can use that will look at the following in an excel cell Daily Numbers Report - Summary_2014-01-26.xls
and then convert that into the date value 41,300. And the same thing for all other cells with the same format...so if was Daily Numbers Report - Summary_2014-01-23.xls the formula would convert it to 41,297.
View 3 Replies
View Related
Apr 3, 2014
I have two columns with entries consisting of numbers. I'm trying to match between the columns but some names dont match because of the formatting.
Example
065 matches 065 (I have that green triangle on the cell that indicated "The number in this cell is formatted as text")
However 120 doesn't match with 120 (only one of them have that green triangle)
I tried to format all the cells as text at it seems the match only works when the green triangle is available. The green triangle seems to only show its self when I double click on the cell. I have thousands of entries so that doesn't work. I also tried to use the "Text to Columns" but it coverts entries like 002 into 2 which I dont want.
View 7 Replies
View Related
Dec 30, 2008
I want to be able to "count" apples and oranges. Is there a way to record a particular text in a text formatted cell and count it. e.g. 10 cells, 3 say "apples", three say "oranges", 4 say "plums". I want excel to keep track of the three types of fruit when I change them and give me a running number of each.
View 2 Replies
View Related
Jan 6, 2009
I want to find strikethrough text and replace it with blanks. In my sheet there are cells that contain both strikethrough and normal text. I tried using the 'Find and replace' tool, specifying the format. I've attached a picture with the settings from the Replace window.
The problem is that Excel finds the cells that contain strikethrough text, but replaces with blank ALL the cell content. I would like to replace only the strikethrough text from the cell and leave the normal text as it is!
View 4 Replies
View Related
Nov 29, 2013
I have a graph that pulls data from a toggled list. Values include currency, percentages and general numbers. The data is set up as text to report as currency, percentages and general numbers. Is it possible to have a graph read these text values?
View 1 Replies
View Related
Mar 18, 2008
I am in need of a formula.
I am subtracting one number from the other and if the result is negative, “Short” otherwise “Add”, I want to use subtracting result in the formula. So for example A1 has 50,000 and A2 has 40000 so the formula in cell A3, should say add $10,000.
Or something like this, =IF(A1-A2>0,”ADD”,”SUBTRACT”,”&TEXT(A1-A2,”$#,##0.00”)
View 9 Replies
View Related
Sep 17, 2009
The solution below to look up numbers in an array formatted as 10 characters as text.
=VLOOKUP(TEXT(A1,"0000000000"),LOOKUPTABLE,2,FALSE)
This has worked well except now I have received the data and the text I want to lookup has been reformatted (previously leading zeros) to the number with trailing spaces, still a total of 10 characters but the above formula no longer works. Is there an easier workaround other than using "Find" to locate the position of the first space.
View 9 Replies
View Related
Jul 2, 2008
I had a hard time to put a suitable title to this query, not sure if above is good enough for my query. I am entering combination of text and number in a cell using a formula. I am wondering if it is possible to add formula which can format number such that it is displayed in accounting format. Example:
Expected output = Trial 1,000
Using this formula
="Trial "&Sheet1!A1
where A1 in Sheet1 = 1000
Output is Trial 1000
View 6 Replies
View Related
Jan 18, 2012
I am attempting to automate a daily extract for account transactions. This extract ranges from 100 - 15000 lines on any given day. Below is an example of my data:
Account Action to be taken
8523 Needs to be converted to a number
84A2 No action needed
8523 Needs to be converted to a number
0749 Needs to remain as text stored as a number
2GP1 No action needed
8181 Needs to be converted to a number
0489 Needs to remain as text stored as a number
[code]...
As you can see, I have three types of data:
1) accounts with leading zeros which need to be stored as text
2) accounts with mixed formats which can remain as text
3) accounts that are purely numerical without leading zeros. I need to convert these from text to numbers.
Since the extract can be up to 15000 lines long, going through the data to manually convert the appropriate cells from text to numbers is unreasonable, especially when this has to be done daily.
Is it possible to script this task? I've tried a number of things, but unfortunately my knowledge of VB isn't very extensive. This is the best that I've come up with:
Code:
LastRow = Range("a" & Rows.Count).End(xlUp).Row
For i = LastRow To 1 Step -1
If Cells(i, "a").Value "0*" Then
Cells(i, "a").Format.General
End If
Next
But I'm failing on the line highlighted in red.
View 3 Replies
View Related
Feb 17, 2012
How to change a "ddd' formatted cell and change it to a text string (even if you have to use another set of cells)?
I have 3 columns of cells A,B,C.
Column A has dates in it.
Column B has the "ddd" format of the A cells (takes date from A cell and turns it into the day of the week...ex:2/17/2012 into "Fri")
Column C is the one I want to formatt whatever is in the B column into a string, such as "Fri" but with it being text instead of "ddd" formatted.
View 4 Replies
View Related
Feb 6, 2014
To input high volumes of dates I change the number format of cells to "00/00/00" so I can key mm/dd/yy without having to hit the slash key. In order to convert these values into an actual date, I've been using the =TEXT function because it allows me to format the text as "00-00-00" which preserves the numbers but inserts a hyphen between each set. From there I use text to columns to break each pair into its own column, and use the =DATE function to combine all 3 into a date.
Is there a way to automate this conversion process in VBA? Alternatively, I'd be open to a different method to input dates in the mm/dd/yy format without having to press the slash key.
View 2 Replies
View Related
Jan 2, 2009
How can I interrogate the contents of a cell to determine whether or not it contains a number formatted as text? I intend to run a .value = .value over each such cell to convert it to a number format, but I only want to do this to relevant cells.
View 9 Replies
View Related
Jan 28, 2009
Is it possible to delete the work 'Strike' purely based on it's formatting I.E: as having a strike through set against it?
Im thinking it's not! I know you can use 'Find and Replace' for single cells with single words in, but not out of a sentence?
View 9 Replies
View Related
May 1, 2007
I was wondering if anyone could tell me how to do away with the "Exponential" appearance of numbers (in Excel) when they are formatted as text. I am working with National Stock Numbers and there are no required computations based on them being formatted as a number ... AND ... one of my constraints is that they are required to be formatted as text when imported into an Access database ...
View 5 Replies
View Related
Mar 19, 2008
I am trying to get a single cell to display the following:
Last Updated: 3/18/2008 15:08 (GMT+2)
Entering =NOW() in a cell displays the date and time as required.
But entering ="Last Updated: "&NOW()&" (GMT+2)" displays the date and time as a serial number. Formatting the cell to Date does not change the serial number to date and time format.
The only way I have found to get the desired result is to use =NOW() in another cell (F13), format that cell to general to get the date/time serial number, then use ="Last Updated: "&TEXT(F13,"m/d/yyyy h:mm ")&" (GMT+2)" in the required destination cell.
As I said this works, but it strikes me as an inefficient method. Is there a formula I can enter or formatting I can apply to get the desired result without using an addition cell?
The result needs to be in a single cell. Splitting text and date/time into 3 adjacent cells will not work with my worksheet setup.
View 9 Replies
View Related
Apr 21, 2013
I am calculating service dates for trucks: Need to make the text in a cell turn red after the 90th day? A
View 3 Replies
View Related
