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.
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)
Case "C"
fc = 1: fb = True: bc = 4
Case "O"
fc = 2: fb = True: bc = 3
Case "D"
fc = 2: fb = True: bc = 46
Case "G"
fc = 2: fb = True: bc = 5..................
I have a spreadsheet where I have certain cells that contain a link to copy a number from another tab in the spreadsheet.
At times, I have to plug in a number to try certain values. In doing this, I overwrite the link or formula. I then do an 'undo' to bring it back to the original link or formula.
I would like to put a conditional format based on the cell's content. If it is the formula, leave it as is. If it is overwritten by a number, I want it to change colors to remind me to undo my change when I am done.
The conditional formatting options I have found so far allow me to format according the the value in the cell but not the nature of it's content: number of formula.
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 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.
I want to create a conditional format formula which is based on values in 2 cells.
Example:
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.
I am lookin to apply CF to a cell based on the value of the left two numbers in a concatenated formula. If the value of the left two numbers are greater than zero the apply the CF
View 6 Replies View RelatedI am wondering if I could have a formula be used against a certain row/cell containing a certain "phrase" or "number" for instance
I want E1 to read something like this = (row containing item "FF32105") (the given row from the previous statement) ( the input column ex. B) (the input value ex. *6)
Essential I want to be able to copy and past a sales forecast and have the formulas automatically (listed below the pasted forecast) calculate purchase needs. The issue is that if our forecast one month has a certain item and then the next does not then they will end up on different lines after the copy/paste. We have over 500 items so a manual adjustment would be time consuming..
Is there a possible way for the formulas to "find" the correct line to start calculating data?
Format Random Number Cells As Draw Result Entered ...
View 9 Replies View RelatedIs 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.
Possible???
I have established that to have more than 3 conditions in my version of Excel (2003) i have to use VBA. Unfortunately I have very little knowledge of this process. I have attached an example of the worksheet I am working on. The worksheet in question totals hours worked for payroll. problem:
Required result: Cell to change to 1 of 8 background colours in response to selection of 1 of 8 options in drop down list in the cell directly next it.
i.e. (in reference to attachment - sheet DATA)
IF E6 = Holiday, then D6 = Red
IF E6 = Half Day Holiday, then D6 = Red
IF E6 = Sick, then D6 = Blue
IF E6 = Sick Half Day, then D6 = Blue
IF E6 = Bank Holiday, then D6 = Green
IF E6 = Compassionate Leave, then D6 = Pink
IF E6 = Unpaid Leave then, D6 = Yellow
IF E6 = Unpaid Leave Half Day then, D6 = Yellow
However, the same basic table as shown in sheet DATA appears several times on the worksheet. And in each instance the conditional formatting is the same (in reference to the relative cells).
I'm using Excel 2007.
I have a field with a "YES"/"NO" drop-down box. There are three fields below which have some default formatting (thick purple dot-dot-dash borders). When the drop-down field is "YES", those three fields should change borders to a single thin black bottom border line.
OK... so that's the setup. And I should note here that it does in fact format properly. However, the problem I'm having is that the screen does not update / repaint with the new formatting. The thick purple dot-dot-dash borders remain, for the most part.
If I page down, then page back up, the new formatting appears just fine. When I switch from "YES" to "NO", again, the formatting changes, but the screen does not update/repaint so the only way to see the new formatting is to again... page up and then page down again.
I'm wondering if anyone knows a way to force the screen to repaint/update so that my users will see the new formatting without having to page up/down.
I am trying to use the status of Conditional Formatting to toggle on/off protection for a cell.
I have a cell with Conditional Formatting applied if the result of a formula is true. If the Conditional Formatting is applied, I want the cell protection turned on so a user can not change the entry in the cell. If Conditional Formatting is not applied (false), cell protection is turned off and the user may edit.
I am using Excel 2010 and Vista.
I have five fields that have a conditional format applied to them: (see attached).
Columns J, K, L, N and O. These conditionals highlight if a minimum number is entered. Ex. Minimum pushups for a 30 y/o male is 27, if a 26 is entered it highlights red. The total score (where I need this to perform) is calculated in column Q. It will format red if the total score is below 75 but what I can't figure out is how to make it format if any of the previous minimums have not been met.
Ex. In cell L4, his crunches were 25 (a automatic failure -- red formatting). Now the total score is above 75 so it calculates as passing (green), but I need it to highlight red regardless because of the minimum not met in cell L4.
I am trying to create a number format for formatting resistor value in their most commonly displayed form. For example 1,200 ohms would be displayed as 1.2kÙ and 5,000,000 ohms would be displayed as 5MÙ, and finally 5 ohms would be displayed as 5Ù. I have been able to create a format that formats any 2 contiguous formats but not all three.
for example: [>999999]###.0,,ÌÙ;[<1000000]###.0,kÙ;
or: [>999]###.0,kÙ;[<1000]###Ù
both work.(other similar criteria work as well) But if I try to put 3 conditions describing the desired formatting I get and error (containing no useful info) as I try to close the formatting dialog. Is there a limit to the number of conditions that can be used. Or something else I'm overlooking.
I want to know if there is any way to highlight a cell that does not contain a whole number. In my spreadsheet I have a formula that calculates the number of packages in an order, but if the result is not a whole number then I want it to be highlighted.
View 4 Replies View RelatedCan I use conditional formatting to determine if a number in a cell is displayed as a normal decimal or scientific?
So a number of 1000 or above will be changed to scientific notation with 4 significant figures.
The conditional formatting just seems to be about colour/shading/icons
I have got following numbers data and I want any number between the ranges repeat again or dupliacated it would highlighted as mentioned
ABC1Start RangeEnd RangeQty221002199100323002349504250025252652710271910621102299190729092929218234823492
As you can see yellow highlighted Ranges between the ranges are repeated again or duplicated that is why they are highlighted
I want to subtract column B from column A and put the result in column C. If the result is a negative I want a formula to turn it to 0.
View 4 Replies View RelatedI have a simple formula in a cell, just a sum of some columns. (=C6+H6) If the sum happens to be greater than 10, though, I want it to show as the number 10. How can I do this?
View 3 Replies View RelatedI need to add more conditional formatting to my spreadsheet. But it seems to belimiting me to 3. Is there a way of increasing it?
I would like to change the number format of a cell in function of the value of another cell. For example, if A1=1, then I would like the cell A2 to be displayed as a percentage. If A1=2, then would like the cell A2 to be displayed as a number.
View 4 Replies View RelatedIn Excel 2003, I have a series of complex(ish) lookups which populate cells in Sheet 1 from other sheets in the same workbook. Some of these cells are %ges some are not, I need to display the resultant sheet values as %ges or not based on if the cell to the left (also populated by a complex(ish) lookup) contains the character "%".
I cannot just format the cell on its own, as the list in itself is volatile and can change according to the complex(ish) lookup,
The main problem with this is I need the result to be a numeric value and NOT text in ALL cases as further calculations will be undertaken on them.
In simple if it is a %ge I need to see the %ge and use it as a %ge or if it is just a number see it as a number and use it as a number.
e.g
A B Result Req
Satisfaction with telephone access (%) 0.900032390.0%
Ability to see boss within 48 hours () 0.889 0.889
I am using a spreadsheet which imports information from an access database then calculates prices based on that.
At the moment in my column "0" I am referencing the number values in column CT from my access database (which gives me a standard retail price)
Code:
=IF(AF5="","",IF(OR('Line list'!CT2="",'Line list'!CT2=0,'Line list'!CP2=0),"0",'Line list'!CT2))
And then I use the following formula to work out the correct prices wholesale prices for different countries based on that.
Code:
=IF(AL5>1,AL5,IF(AK5
I want to be able to change the font color of the numbers in a column based on even or odd. I selected the columns and formated them to all be blue... thats the color I want for even, but now I can't figure out how to set a condition that checks for odd numbers and changes them to red. I now have a count of even/odd for all records, and even/odd for the last 30, this provided some very interesting results.
View 9 Replies View RelatedI have used Formula to ID cells containing a formula to flag cells red with conditional formatting in a list that do not have formula.
I need a single cell to change colour if any of the cells in the list do not have a formula. e.g. the subtotal in the top row could go red so the user can scroll down to find individual red cells.
Is there a way to count the red cells without formula in the list - Conditionally formatted cells are hard to count. I don't want to use a vba script to do it as the user probably won't run it and auto run slow calculations down.
Is there an array formula that can return true or false if not(CellHasFormula) is true anywhere in the list?
countif(AH1:AH2976,CellHasFormula) doesn't work.
i have a spreadsheet which is used to keep the training records for the company it counts no of staff and their skill levels for different parts of the job, when a person moves to a different branch the skill required changes and they have a set period of time to aquire the new skill.
when the time is up the colour of the old skill is changed to a red font by a conditional format based on the date.this is then counted by a calculation so the team know how many people are to be targeted for extra training.
the problem i have is the calculation will not count conditionally formated cells, so whenever i update the sheet i have to go to each cell with red text and change the font colour manually to red, this sounds really silly and is impossible to explain to anyone who does not understand conditional formatting. i e the people who will end up using the database
what im looking for is a way to count conditionally formatted cells based on the condition being met i.e. they are showing as red font.
I have a row of cells pertaining to jobs B3 across to I3.
In B3, C3, D3, E3 the value entered is text.
In F3, G3, H3, I3 the value entered is time.
What I am trying to do is to change the Background colour to Red on cells
F3, G3, H3, I3 if either H3 or I3 or are empty at the end of the current day but only if there is some text in any or all of the cells B3 - E3
So for example if H3 or I3 have no time put in them by 23:59 today, when the workbook is opened tomorrow the cells background colour will turn red.
Not sure if this if poossible or not? hopefully I have explained it well enough but if not pease let me know.
I have the following code. I am trying to conditional format a row based on the value of the A column of the row.I have a total of 3 rows. I have to use VBA because in time I may have 4 rows of data. The problem is I after the first do while loop the code stops and the code doesn't proceed to format row 2.
View 2 Replies View RelatedIm having some issues with some code Im working on. My goal is to delete the rows that do not have conditions met. Conditional formatting is used in 6 columns of my spreadsheet and the cell fill is to turn red when below .50 . This code is shown below. What I need to do now is look at the 6 columns where the conditions are applied, and delete any rows where the column cells are not red. To further clarify, if in row 1 column X:X does not meet criteria, but column AL:AL does, the row would not be deleted. However if none of the columns meet the criteria, the entire row is deleted.
View 6 Replies View Related