Conditional formatting using VBA in Excel 2003?

Question

Sorry about the confusing title, but I’ve never had to use VBA or Macro’s in excel before
What im trying to do is use conditional formatting, and since theres more than 3 conditions, I have been told I have to use VBA to accomplish this
My formatting is as follows
1 Cell N2 =N2<>””  Magenta, Text Color Automatic, or black
2 Cells range B2:P2 =M2<>””  Grey, Text Color Automatic, or black
3 Cells range B2:P2 =H2<>””  Gold, Text Color Automatic, or black
4 Cells range B2:P2 =G2<>””  Cyan, Text Color Automatic, or black
5 Cells range B2:P2 =F2<>””  Red, Text Color White

These are listed in the order of Importance 1 being most important 5 being least (if condition for red and grey are both met, Grey will show, whereas if conditions for red, cyan, and gold are met, gold will show, if all conditions are met then the row would be all grey except one magenta cell) The Color fill should only happen on a row by row basis for columns b-p on basically the whole spreadsheet.

Answer 1

Edit: Sorry, but I didn't see any reference in your question to an interior color fill of 'Green'. The assumption was, in light of the absence of that bit of information, that there was no other 'filling' occurring.



So, are you saying that if any cell has an interior color of 'Green' to leave it green? If so, which green? Dark Green, light green, Green, etc?



Also, please provide any/all other details about the structure of your worksheet that may have a bearing on total functionality.





==========================







This event handler might do as you ask.



It will color fill in the order you have listed. So, if N2 is not blank it fill magenta regardless of other conditions being met 'down stream'.



If M2 is not blank, it will fill gray regardless whether other conditions are met 'down stream'.



If M2 is blank and H2 is not blank, it will fill Gold, regardless of other conditions 'down stream'.



Etc.



If is not your goal, please provide a detailed synopsis of the multiple condition situations. Otherwise,



Copy this code to the clipboard:



Private Sub Worksheet_Change(ByVal Target As Range)

Dim i, j, LastRow

LastRow = Range("B" & Rows.Count).End(xlUp).Row + 1

Application.ScreenUpdating = False

For i = 2 To LastRow

For j = 2 To 16

If Cells(i, "M").Value <> "" Then

Cells(i, j).Interior.ColorIndex = 15

cIndex = 15

ElseIf Cells(i, "H").Value <> "" Then

Cells(i, j).Interior.ColorIndex = 45

cIndex = 45

ElseIf Cells(i, "G").Value <> "" Then

Cells(i, j).Interior.ColorIndex = 8

cIndex = 8

ElseIf Cells(i, "F").Value <> "" Then

Cells(i, j).Interior.ColorIndex = 3

Cells(i, j).Font.ColorIndex = 2

cIndex = 3

Else: Cells(i, j).Interior.ColorIndex = xlNone

End If

Next j

Next i

For i = 2 To LastRow

If Cells(i, "N").Value <> "" Then

Cells(i, "N").Interior.ColorIndex = 7

Else

Cells(i, "N").Interior.ColorIndex = Cells(i, "B").Interior.ColorIndex

End If

Next

End Sub



Select the appropriate worksheet and right click the sheet tab.



Select 'View Code'



Paste the code into the editing area to the right.



Close the VBE and return to the worksheet.



Enter values in columns F, G, H, M, and the rows will color fill as coded.

Answer 2

Excel 2003 Conditional Formatting More Than 3

Answer 3

William is right. For conditional formatting in Excel 2003 you can't use over three different colors. So you'd have to use a macro in that instance. By the way, I noticed that you skipped over 90%. Here is a macro that should help you: Application.ScreenUpdating = False Dim Grade As Double For Each x In [Sheet1!B:B] If IsNumeric(x) = True Then Grade = Round(x, 0) If x.Text <> "" Then Select Case Grade 'Yellow Case 0 To 69: x.Interior.ColorIndex = 6 'Blue Case 70 To 80: x.Interior.ColorIndex = 41 'Red Case 81 To 90: x.Interior.ColorIndex = 3 'Green Case 91 To 99: x.Interior.ColorIndex = 4 'Orange Case Is >= 100: x.Interior.ColorIndex = 45 Case Else: x.Interior.ColorIndex = xlNone End Select End If End If Next x Go to Tools > Macro > Record New Macro Then pick out cells A1 to A5 and color them the colors you want for the different grade ranges. Then hit the stop button. Now go back to Tools > Macro > Macros... A window should pop up. Select the macro you just created and click Edit. This will bring up the code for the macro you just recorded. Write down the color index numbers for the colors you chose based on the code the macro recorded. Now delete out all of the lines of code except the lines tha have the word Sub in them. Paste my code in between the two lines that have Sub in them. Then replace my index numbers with the ones you just wrote down. Change Sheet1 to whatever the name of your work sheet is. Then click the save button and close the window with the coding. Click save on your workbook. Now go back and click: Tools > Macro > Macros... But this time you'll click the play button for the macro. This will run the macro you just created and colorize all of those grade cells in Column B. Important Note: It's always good to save your work or save your work under a different name before you run a macro. You can't go back and hit the undo button to undo the changes that a macro made.