I would like to create a cell that will tell me if a dimension has failed.
I have a tolerance that the value must be between and if it is not then i would like a cell that will input an X
I would like to change the name of all cells withen xrange with a text value greater than nothink.
View 4 Replies View RelatedIf I wanted to highlight a cell if any cell in the range to the right was greater than zero, what formula would i use. I have tried =IF(L1:AD1>0,1) with the result returning for only the cells in column L. Row 3 has no value in column L but a value in column N with no result to highlight the cell.
View 4 Replies View RelatedI’m trying to write a macro to look down two different columns in my work sheet and if the is a value >0 then copy a set range of cells to another sheet,
I need to search column (k) and (x) range (“K2 : K147”) and (“X2 : X147”) in (sheet1) for values >0 if there is, then copy from (“f : m”) if it is found in the (k) column, or copy from (“s : z”) if it is found in the (X) column, and paste value only to the next empty cell in sheet2 . Sheet2 is empty so (A) on down is fine for paste range. There is a spin button in the copy range column (L) and I don’t want that picked up in the copy. Manually I (paste text) only but I think (values only) will do the same thing.
[Code] .........
Basically I have three sheets. MAIN, Sheet 1 and Sheet 2
Sheet 1 and 2 are in the same format
A3 down is a list of country names and then B3:I71 contains the data im interested in.
I've been trying to create a function that looks at B3:i71 to see if any cell in that range contains a value greater then $0.00. If it does then the row that contains the cell with a value greater then $0.00 (between col A to K) should be copied to sheet MAIN from cells B3 down. This should ultimatley produce a list of data for any row containing a value greater then $0.00. This process should then be repeated on Sheet 2 and should join the list below sheet 1.
I have a certain range to start, and want to exclude rows if a defined cell is not greater than zero. I cannot figure out the syntax to achieve. The following code selects the range even if the single cell is NOT greater than 0...
View 5 Replies View RelatedI am trying to use the SUMIF function in excel where I want to sum a range of cells greater than whatever is in cell D8. Here is what I tried to use, but it doesn't work. SUMIF(A2:A10,>D8,B2:B10)
The content in D8 can change because the user makes a choice from the drop-down list and a number pops up in D8 that is referenced to their choice.
Is it possible to insert text an a cell value in conditional formating, i,e Ive got the conditional formatting:
=AND(H$4>=$B5;H$4<=$C5)
I want to insert this text whenever this condition is true once and not to repeay it:
="Load " &TEXT(G$5;"dd-mmmm") -- where G$5 is a vallue cell_
This is a conditional formatting problem I have not been able to resolve:
I have a range, say A1:N30
Each cell contains a text/number combination.
I would like to highlight each cell on a specific row if the vale exists within the row above - the issue is that the cell values are not in the same columns.
I have attached a sample workbook with the desired output.
Conditional formatting.xlsx
I have to make a table that shows that a if someone purchases
less than 5 items they receive no discount
5-10 items they receive 2% discount
11-20 items they receive 5% discount
21-50 items they receive 8% discount
over 50 items they receive 10%
and it has to be done in a way that the discount rate can be calculated using Vlookup I am struggling to find the best way to write this table. i tried numbering 1 to 50 and writing the corresponding discount rate in the second column but this looks untidy and can't calculate greater than 50 as i am not sure how to write it in the cell so it reads as >50 and not just 50.
I have some data generated from same sample as listed below:
A: Amplitude
Time Time(hours) Y (A1) SD (A1) Y (A2) SD (A2) Y (A3) SD (A3) 3.97 95.28 0.01 0.00 0.01 0.00 0.01 0.00 3.98 95.53 0.01 0.00 0.01 0.00 0.01 0.00 3.99 95.78 0.01 0.00 0.01 0.00 0.01 0.00
B: Beat
Time Time(hours) Y (A1) SD (A1) Y (A2) SD (A2) Y (A3) SD (A3) 3.97 95.28 1.00 0.00 0.01 0.00 0.01 0.00 3.98 95.53 2.00 0.00 0.01 0.00 0.01 0.00 3.99 95.78 3.00 0.00 0.01 0.00 0.01 0.00
Is there any easy macro to combine data under A and B. For example:cut A1 data under B:Beat and insert it after A1 under A:Amplitude; cut A2 data under B:Beat and insert it after A2 under A:Amplitude, et al?
I will have a list of unique values. For each unique value, there is an indicator that tells me how many lines of information I need. Based on that criteria, I need a macro to insert that many rows and populate it with text. This will ultimately become a look up table that I will just use index/match function to populate.
I've attached a sample sheet. I'm not sure if I need a macro to insert rows or a macro to paste a copied range. Or maybe I need a mixture of both with a formula to calculate the number of cells I need?
I would like to Insert the contents of a Range of Cells into a single Comment. ie:
The contents of Range A1:A50 into a Comment placed at F1. Is there a Macro that will do this?
I'm looking for a quick and easy way to insert a value in the first empty cell in a multi-column, multi-row range using VBA. I suppose I could loop through the range, but the table could grow to immense proportions and I don't want to slow everything down.
For example, the new value should be placed in cell C4. It doesn't matter whether the range is looped through the rows or columns, either will work just fine.
A
B
C
1
5
65
56
2
32
12
89
[code]...
is possible to have a cell's text color change if the value of the cell is greater than a certain amount. For example, if the cell is $200 or more the text would change to red.
View 3 Replies View RelatedTrying to create a button that, once clicked performs the following task:
Checks whether the text in a cell in the column X2:X40 matches the text in a cell in the Range A2:U14.
If it does, the formatting of the cell in X2:X40 (eg background) must change to match that of the corresponding cell in A2:U14.
Only 1 cell in the range will match 1 cell in the column
For example:
Before click:
Cell A9 has "John" written in it and a yellow background
Cell X4 has "John" written in it and a clear background
During click:
matches these cells
changes X4 so that it also has a yellow background
After click:
Cell A9 has "John" written in it and a yellow background
Cell X4 has "John" written in it and a yellow background
I have a range of data on sheet1 say A1:A10 with random letters in each cell. On sheet2, I also have random letters and number in column A. What am trying to do is find a formula which if any of the data on sheet1 can be found in say A1 of sheet2 then shade the cell a given colour or if formula in B2 then show True otherwise false if not found. then do the same for the remainder of the cells on sheet2 which am assuming will be to just copy the formula in B1 of sheet2 or the conditional format in A1 whichever comes first.
View 4 Replies View RelatedI'm trying to format a range of cells based on a single cell. Below is the code that
I am using:
Private Sub Worksheet_Change(ByVal Target As Range) ...
Summary of performance of various products against target is as follows,
Product vs Target
Color Code
Result
CH4OH
Green
1.0
[Code] ........
I need the final result automated as follows,
If 2 green of the 4 products, then final result Gree
If 2 Amber of the 4 products, then final result amber
If 2 Red of the 4 products, final result Red
Is there a way to automate this?
So I've applied conditional formatting to columns A and B to highlight and bold any blank entries. However since those guys tend to show up a thousand rows or so down I don't have anything to alert me that there is a problem. I know that I can just filter those columns to check but I'm not the only one using this sheet so I'd really like to have a visual cue.
Is there a way to have cell J1 highlight if any of the cells in the range (A2:H3000) have been bolded?
I have two sheets (Results and August) with numbers in column A, I want to change the color of the cell on Results if its contents matches the number on August. I was feeling adventurous today, so I tried writing some VBA code, but it changes the color of ALL the cells. What am I missing?
Sub FindMatches()
'Compares student numbers on Results sheet to those on August sheet; if match is found then highlights the student number
Dim Sht1Rng As Range
Dim Sht2Rng As Range
Set Sht1Rng = Worksheets("Results").Range("A1", Worksheets("Results").Range("A65536").End(xlUp))
Set Sht2Rng = Worksheets("August").Range("A1", Worksheets("August").Range("A65536").End(xlUp))
Set d = Nothing
For Each C In Sht1Rng
Set d = Sht2Rng. Find(C.Value, LookIn:=xlValues)
If Not d Is Nothing Then
Sht1Rng.Interior.ColorIndex = 10
Set d = Nothing
End If
Next C
End Sub
I suspect that I shouldn't be using sht1rng.Interior.Colorindex, as I think that may color the entire range? What should I use instead?
I have a spreadsheet that records "Audit Date" in column C & "Audit Due Date" in column D. "Audit Due Date" is always 2 years from "Audit Date". I think I learnt the correct forumla to use =EDATE(C2,24). What I would like Excel to do is to highlight "Audit Due Date":
Red: If the audit was completed 1.5 years+ Amber: If the audit was completed between 1 & 1.5 years Green: If the audit was completed less than 1 year The colour key will show me which audits need chasing.
how to include a condition in the condition? What I mean is in this set of numbers:
1000(A) 1000(B) 1000(C) 1200(D) 500(E) 1000(F)
I want excel to point out (with a 1, True, x, whatever) if (B) is greater than (A) by 10% or more and if (C) is greater than (B) and so on. I know I cant use =if(C>B,1,0)...Im just unsure how to include the % in the function
I have a protected worksheet with most cells locked and some that are unlocked. I also know the password to unlock the sheet. VBA code to monitor a cell(B29 in my case) and if it has a value of 6.00 or more than it will unlock cell B34?
View 7 Replies View RelatedI have a series of columns (L - X) each representing a diagnosed disorder (Dx), coded as binary, with 1=Positive Diagnosis (Success). The reference cell contains diagnostic codes; for each respective Dx column I need a 1 or 0, obviously. Each Dx category has a range of values (Dx codes), so I need to write syntax that reflects this range. For example, =IF(A1=>141,AND(A1=<239.99)),"1","0")
In other words, if cell value is 141 through 239.99 then return 1, else 0
Would it be something like this:
=IF(AND(A1=>141,OR(A1=<239.99)),"1","0")
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.
View 2 Replies View RelatedI 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 need to format cell D1 to have the fill color be red if any cell in D2:D21 is blank. I've tried
=VLOOKUP("", $D$2:$D$21, 1, FALSE) but that returns #N/A (there is one blank cell in the range at the moment).
I really don't want to use =OR($D$2="", $D$3="", ...$D$21="") if I can avoid it.
I have a range of cells where each row relates to an employee.
In Cells (Columns H:L) I want the range of cells on that row to turn blue if the user puts a "H" in one of the cells.
Also it will need to be adapted for "O" , "Y", "X" - each with different colours.
This needs to be repeated on each row for each person.
I would like to add a date to one cell (say A6) and have this do two things:
#1) this would add "Closed" to a given cell such a A5.
#2) and this would add a color to a group of cells like A1 through A8.