excel conditional format or formula help Closed

Question

Hello, I need help for conditional formatting or a If/then/And/or formula that can assign numbers for multiple criteria. How can I set up a blank column (column C) to automatically fill colors (or numbers, less ideal) according to the following 3 criteria from values in columns A and B: if column A is >=2 AND column B is >=80 code it green (or in absence of colors the #2) if column A is >=2 and column B is <80 OR Column A is <2 and Column B is >=80 code it yellow (or #1) if column A is <2 AND column B is <80 code it red (or #0) can this be done? can anyone please help?? thanks a million!

venkat1926 · Answer

in excel 2003 or earlier versions conditional formatting (format-conditionaformatting) can work on only three conditions. so a macro is required. I do not know whether by a formula you can color a cell. so if you want to put a number in colulmn C then you can do it suppose the data is as follows from A1 1 62 9 76 3 43 10 71 3 70 8 96 2 52 1 93 9 64 1 74 then in C1 copy this formula =IF(AND(A1>=2,B1>=80),2,IF(AND(A1>=2,B1<80),1,IF(AND(A1<2,B1>=80),1,IF(AND(A1<2,B1>=80),0,"")))) copy C1 down if you are interested only in color run this macro keep the sheet as active sheet and run the macro Sub test() Dim r As Range, c As Range Set r = Range(Range("A1"), Range("A1").End(xlDown)) For Each c In r If c >= 2 And c.Offset(0, 1) >= 80 Then c.Offset(0, 2).Interior.ColorIndex = 4 If c >= 2 And c.Offset(0, 1) < 80 Then c.Offset(0, 2).Interior.ColorIndex = 6 If c < 2 And c.Offset(0, 1) Then c.Offset(0, 2).Interior.ColorIndex = 6 If c < 2 And c.Offset(0, 1) < 50 Then c.Offset(0, 2).Interior.ColorIndex = 3 Next c End Sub

Excel conditional format or formula help

1 response

Similar discussions

Subscribe To Our Newsletter!