Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement - compare to coloured cell
Hi Everyone
I'm not sure how to write an IF statement. If Cell A1 is shaded RED, I want cell B1 to to say OK If A1 is not red, then no message. The IF statement is obviously in cell B1. As far as I can see, conditional formatting will not work, because I want a message in B1, not a format. Any ideas? many thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement - compare to coloured cell
LinLin
You cannot write an IF statement formula that looks at the color of the cell. VBA can do that, but not a formula. Perhaps you can work with the condition that makes that cell colored? Come back if you want to try a VBA solution. Provide more detail about what you have and what you want to do. HTH Otto "LinLin" wrote in message ... Hi Everyone I'm not sure how to write an IF statement. If Cell A1 is shaded RED, I want cell B1 to to say OK If A1 is not red, then no message. The IF statement is obviously in cell B1. As far as I can see, conditional formatting will not work, because I want a message in B1, not a format. Any ideas? many thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement - compare to coloured cell
Thanks Otto
That's what I suspected because I had a look at a lot of posts and nothing had any info like this. This is my problem: I have a cell (A1) in which you can enter a number, and for arguments sake, if the number is 10, the cell will turn red. I already have that set up using conditional formatting. I would also like a statement to appear in the cell (B1) below which says "value not valid" So, normally, I would say If A1=RED, then "Value not valid" etc. Conditional formatting is for formatting only. I can't see any other way to add a Text Warning if required? Thanks again! "Otto Moehrbach" wrote: LinLin You cannot write an IF statement formula that looks at the color of the cell. VBA can do that, but not a formula. Perhaps you can work with the condition that makes that cell colored? Come back if you want to try a VBA solution. Provide more detail about what you have and what you want to do. HTH Otto "LinLin" wrote in message ... Hi Everyone I'm not sure how to write an IF statement. If Cell A1 is shaded RED, I want cell B1 to to say OK If A1 is not red, then no message. The IF statement is obviously in cell B1. As far as I can see, conditional formatting will not work, because I want a message in B1, not a format. Any ideas? many thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement - compare to coloured cell
Actually, my last post makes it sound too easy.
I am making a form to help people set up an account number in a chart of accounts. The cell A1 tells them if the number they select is already used. So in conditional formatting I've set up a "countif" formula which looks at the existing list of account numbers and returns a RED cell if it matches. (I thought I'd better add this because this is why the IF statement is complicated, the list of numbers to compare to is too long, but if I could get it to compare to the Colour Red, my life would be simplier!) thanks! "Otto Moehrbach" wrote: LinLin You cannot write an IF statement formula that looks at the color of the cell. VBA can do that, but not a formula. Perhaps you can work with the condition that makes that cell colored? Come back if you want to try a VBA solution. Provide more detail about what you have and what you want to do. HTH Otto "LinLin" wrote in message ... Hi Everyone I'm not sure how to write an IF statement. If Cell A1 is shaded RED, I want cell B1 to to say OK If A1 is not red, then no message. The IF statement is obviously in cell B1. As far as I can see, conditional formatting will not work, because I want a message in B1, not a format. Any ideas? many thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement - compare to coloured cell
LinLin
The following formula in any cell will produce "Value not valid" if the value in A1 is present in the range B2:B9. A blank cell will be produced if not. =IF(COUNTIF(B2:B9,A1)0,"Value not valid","") As an example of what VBA can do, the following macro will produce a message box on the screen, saying "The entry is invalid." if the value in A1 is in Column B anywhere from B2 down. The same macro can easily add/remove cell color. As written, this macro will clear A1 if it is a duplicate. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) Dim RngColB As Range If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("A1")) Is Nothing Then If IsEmpty(Target.Value) Then Exit Sub Set RngColB = Range("B2", Range("B" & Rows.Count).End(xlUp)) If RngColB.Find(What:=Target.Value, LookAt:=xlWhole) Is Nothing Then MsgBox "The entry is invalid.", 16, "Invalid Entry" Application.EnableEvents = False Target.ClearContents Application.EnableEvents = True End If End If End Sub "LinLin" wrote in message ... Actually, my last post makes it sound too easy. I am making a form to help people set up an account number in a chart of accounts. The cell A1 tells them if the number they select is already used. So in conditional formatting I've set up a "countif" formula which looks at the existing list of account numbers and returns a RED cell if it matches. (I thought I'd better add this because this is why the IF statement is complicated, the list of numbers to compare to is too long, but if I could get it to compare to the Colour Red, my life would be simplier!) thanks! "Otto Moehrbach" wrote: LinLin You cannot write an IF statement formula that looks at the color of the cell. VBA can do that, but not a formula. Perhaps you can work with the condition that makes that cell colored? Come back if you want to try a VBA solution. Provide more detail about what you have and what you want to do. HTH Otto "LinLin" wrote in message ... Hi Everyone I'm not sure how to write an IF statement. If Cell A1 is shaded RED, I want cell B1 to to say OK If A1 is not red, then no message. The IF statement is obviously in cell B1. As far as I can see, conditional formatting will not work, because I want a message in B1, not a format. Any ideas? many thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement - compare to coloured cell
Hi Otto
That is WAY cool! Thanks so much - works exactly how I want .... except, maybe, if the number is not in the list "Value Not Valid" (that works) but if the number is valid "Number is Valid". I can insert that in the "" in the last part of your formula, but then if there is NO value at all in the cell, it still comes up with "Number is Valid" message. Can I push the formula further to have no message if the cell is blank/no value? Thanks again! I am thrilled the solution was so simple! "Otto Moehrbach" wrote: LinLin The following formula in any cell will produce "Value not valid" if the value in A1 is present in the range B2:B9. A blank cell will be produced if not. =IF(COUNTIF(B2:B9,A1)0,"Value not valid","") As an example of what VBA can do, the following macro will produce a message box on the screen, saying "The entry is invalid." if the value in A1 is in Column B anywhere from B2 down. The same macro can easily add/remove cell color. As written, this macro will clear A1 if it is a duplicate. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) Dim RngColB As Range If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("A1")) Is Nothing Then If IsEmpty(Target.Value) Then Exit Sub Set RngColB = Range("B2", Range("B" & Rows.Count).End(xlUp)) If RngColB.Find(What:=Target.Value, LookAt:=xlWhole) Is Nothing Then MsgBox "The entry is invalid.", 16, "Invalid Entry" Application.EnableEvents = False Target.ClearContents Application.EnableEvents = True End If End If End Sub "LinLin" wrote in message ... Actually, my last post makes it sound too easy. I am making a form to help people set up an account number in a chart of accounts. The cell A1 tells them if the number they select is already used. So in conditional formatting I've set up a "countif" formula which looks at the existing list of account numbers and returns a RED cell if it matches. (I thought I'd better add this because this is why the IF statement is complicated, the list of numbers to compare to is too long, but if I could get it to compare to the Colour Red, my life would be simplier!) thanks! "Otto Moehrbach" wrote: LinLin You cannot write an IF statement formula that looks at the color of the cell. VBA can do that, but not a formula. Perhaps you can work with the condition that makes that cell colored? Come back if you want to try a VBA solution. Provide more detail about what you have and what you want to do. HTH Otto "LinLin" wrote in message ... Hi Everyone I'm not sure how to write an IF statement. If Cell A1 is shaded RED, I want cell B1 to to say OK If A1 is not red, then no message. The IF statement is obviously in cell B1. As far as I can see, conditional formatting will not work, because I want a message in B1, not a format. Any ideas? many thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement - compare to coloured cell
Hi
I assume you want to test if A1 has NO value. =IF(COUNTIF(B2:B9,A1)0,"Value not valid",IF(A1<"","Number is Valid","")) Regards, Per "LinLin" skrev i meddelelsen ... Hi Otto That is WAY cool! Thanks so much - works exactly how I want .... except, maybe, if the number is not in the list "Value Not Valid" (that works) but if the number is valid "Number is Valid". I can insert that in the "" in the last part of your formula, but then if there is NO value at all in the cell, it still comes up with "Number is Valid" message. Can I push the formula further to have no message if the cell is blank/no value? Thanks again! I am thrilled the solution was so simple! "Otto Moehrbach" wrote: LinLin The following formula in any cell will produce "Value not valid" if the value in A1 is present in the range B2:B9. A blank cell will be produced if not. =IF(COUNTIF(B2:B9,A1)0,"Value not valid","") As an example of what VBA can do, the following macro will produce a message box on the screen, saying "The entry is invalid." if the value in A1 is in Column B anywhere from B2 down. The same macro can easily add/remove cell color. As written, this macro will clear A1 if it is a duplicate. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) Dim RngColB As Range If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("A1")) Is Nothing Then If IsEmpty(Target.Value) Then Exit Sub Set RngColB = Range("B2", Range("B" & Rows.Count).End(xlUp)) If RngColB.Find(What:=Target.Value, LookAt:=xlWhole) Is Nothing Then MsgBox "The entry is invalid.", 16, "Invalid Entry" Application.EnableEvents = False Target.ClearContents Application.EnableEvents = True End If End If End Sub "LinLin" wrote in message ... Actually, my last post makes it sound too easy. I am making a form to help people set up an account number in a chart of accounts. The cell A1 tells them if the number they select is already used. So in conditional formatting I've set up a "countif" formula which looks at the existing list of account numbers and returns a RED cell if it matches. (I thought I'd better add this because this is why the IF statement is complicated, the list of numbers to compare to is too long, but if I could get it to compare to the Colour Red, my life would be simplier!) thanks! "Otto Moehrbach" wrote: LinLin You cannot write an IF statement formula that looks at the color of the cell. VBA can do that, but not a formula. Perhaps you can work with the condition that makes that cell colored? Come back if you want to try a VBA solution. Provide more detail about what you have and what you want to do. HTH Otto "LinLin" wrote in message ... Hi Everyone I'm not sure how to write an IF statement. If Cell A1 is shaded RED, I want cell B1 to to say OK If A1 is not red, then no message. The IF statement is obviously in cell B1. As far as I can see, conditional formatting will not work, because I want a message in B1, not a format. Any ideas? many thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement - compare to coloured cell
You assumed correctly - that was excellent also!
I'm still getting my head around nested IF statements, I'll get there one day! "Per Jessen" wrote: Hi I assume you want to test if A1 has NO value. =IF(COUNTIF(B2:B9,A1)0,"Value not valid",IF(A1<"","Number is Valid","")) Regards, Per "LinLin" skrev i meddelelsen ... Hi Otto That is WAY cool! Thanks so much - works exactly how I want .... except, maybe, if the number is not in the list "Value Not Valid" (that works) but if the number is valid "Number is Valid". I can insert that in the "" in the last part of your formula, but then if there is NO value at all in the cell, it still comes up with "Number is Valid" message. Can I push the formula further to have no message if the cell is blank/no value? Thanks again! I am thrilled the solution was so simple! "Otto Moehrbach" wrote: LinLin The following formula in any cell will produce "Value not valid" if the value in A1 is present in the range B2:B9. A blank cell will be produced if not. =IF(COUNTIF(B2:B9,A1)0,"Value not valid","") As an example of what VBA can do, the following macro will produce a message box on the screen, saying "The entry is invalid." if the value in A1 is in Column B anywhere from B2 down. The same macro can easily add/remove cell color. As written, this macro will clear A1 if it is a duplicate. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) Dim RngColB As Range If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("A1")) Is Nothing Then If IsEmpty(Target.Value) Then Exit Sub Set RngColB = Range("B2", Range("B" & Rows.Count).End(xlUp)) If RngColB.Find(What:=Target.Value, LookAt:=xlWhole) Is Nothing Then MsgBox "The entry is invalid.", 16, "Invalid Entry" Application.EnableEvents = False Target.ClearContents Application.EnableEvents = True End If End If End Sub "LinLin" wrote in message ... Actually, my last post makes it sound too easy. I am making a form to help people set up an account number in a chart of accounts. The cell A1 tells them if the number they select is already used. So in conditional formatting I've set up a "countif" formula which looks at the existing list of account numbers and returns a RED cell if it matches. (I thought I'd better add this because this is why the IF statement is complicated, the list of numbers to compare to is too long, but if I could get it to compare to the Colour Red, my life would be simplier!) thanks! "Otto Moehrbach" wrote: LinLin You cannot write an IF statement formula that looks at the color of the cell. VBA can do that, but not a formula. Perhaps you can work with the condition that makes that cell colored? Come back if you want to try a VBA solution. Provide more detail about what you have and what you want to do. HTH Otto "LinLin" wrote in message ... Hi Everyone I'm not sure how to write an IF statement. If Cell A1 is shaded RED, I want cell B1 to to say OK If A1 is not red, then no message. The IF statement is obviously in cell B1. As far as I can see, conditional formatting will not work, because I want a message in B1, not a format. Any ideas? many thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If statement to compare time cell to a time | Excel Worksheet Functions | |||
COUNT COLOURED CELL | Excel Discussion (Misc queries) | |||
Using IF statement compare cell ID with data in another column tru | Excel Worksheet Functions | |||
If statement to compare for wildcard value in another cell. | Excel Worksheet Functions | |||
IF formula & COLOURED CELL | New Users to Excel |