#1
|
|||
|
|||
Any equiv. in Conditional Formatting???
Code:
''' check if each cell in selection does not contain specific words Dim oCell As Excel.Range For Each oCell In Selection.Cells If InStr(1, oCell.Value, "Teacher", vbTextCompare) = 0 And InStr(1, oCell.Value, "Tutor", vbTextCompare) = 0 Then oCell.Interior.ColorIndex = 3 End If Next oCell ''' Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Columns("A").Cells) Is Nothing Then If InStr(1, Target.Value, "Teacher", vbTextCompare) = 0 And InStr(1, Target.Value, "Tutor", vbTextCompare) = 0 Then Target.Interior.ColorIndex = 3 Else Target.Interior.ColorIndex = 0 End If End If End Sub ''' I would prefer Conditional Formatting. But how can I do that like Conditional Formatting? Can I do it without VBA? |
#2
|
||||
|
||||
One way - the conditional formatting formula you could use to do this would be:
case insensitive =COUNT(SEARCH({"teacher","tutor"},A1))=0 case sensitive =COUNT(FIND({"Teacher","Tutor"},A1))=0 Last edited by Colin Legg; 06-03-2012 at 11:22 AM. |
#3
|
|||
|
|||
Quote:
Please take a look at the attached file alongside with the questions below: test.xlsx Ques 1: But in Excel 2010, it does not work in the conditional formatting formula as it returns something like "cannot use array in conditional formatting formula". Ques 2: In Column B, I want to know why Cell B2 returns a value error but B4 doesn't? Indeed, I think B2 shouldn't have returned a value error. And I have come up with a solution in Column E to avoid using array in the formula with the use of "AND". |
#4
|
||||
|
||||
Quote:
Then your conditional formatting rule becomes: =COUNT(SEARCH(Instructors,$A1))=0 Quote:
|
#5
|
|||
|
|||
Quote:
I should give a name to the array in 2010. And you're very specialised in using Excel coz you can tell the little difference between 07 and 10. And sorry. I don't know why the formula causing the error disappears. I cannot retrieve the formula though. Have to forget them. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conditional Formatting. | Laurie B. | Excel | 6 | 04-09-2012 05:01 PM |
Conditional formatting | zanat0s | Excel | 4 | 01-20-2012 03:30 AM |
Conditional formatting with AND, OR | Lucky | Excel | 2 | 10-03-2011 11:41 PM |
Conditional Formatting | namedujour | Excel | 3 | 08-25-2011 01:46 PM |
help with conditional formatting | Snvlsfoal | Excel | 3 | 07-03-2011 11:55 PM |