Excel VB Code Question?

Question

I have this spreadsheet.....

So I have this column say column D and it is a long list of static items, say Apples, Grapes, Oranges, Pears, etc. Then another column say column H that has a drop down to go alongside each item that has a a rating, say, "hate, take or leave, like, love, really love" (you get the idea but there are 8 to chose from)

What I would like to do is change the cell background color of the items in column D based on the drop down selection in H to go from various shades of green to yellows to reds depending on the degree of "liking that item" if that makes sense

The list is long (two tabs in fact) and I have tried to use some other VB code I have in other spreadsheets but no joy so far. Any help with the code would be great

Thanks

Answer 1

You can use Conditional Formatting, if you have Excel 2007 or 2010. Otherwise, you will have to use VBA.



The thing to keep in mind is that there are not a lot of shades of Red in Excel. I would think you would want the bright red to represent your most positive response as it really stands out.



I would also suggest using greens, yellows, light orange, orange and bright red for the 'spectrum' of 'Hate to Really Love'.



Try the following event handler and see what you think.



Copy the following code to the clipboard:



Private Sub Worksheet_Change(ByVal Target As Range)

Dim i, LastRow

LastRow = Range("H" & Rows.Count).End(xlUp).Row

For i = 1 To LastRow

Select Case UCase(Cells(i, "H").Value)

Case Is = "HATE"

Cells(i, "D").Interior.ColorIndex = 10

Case Is = "DISLIKE"

Cells(i, "D").Interior.ColorIndex = 43

Case Is = "SOMEWHAT DISLIKE"

Cells(i, "D").Interior.ColorIndex = 35

Case Is = "TAKE OR LEAVE"

Cells(i, "D").Interior.ColorIndex = 36

Case Is = "SOMEWHAT LIKE"

Cells(i, "D").Interior.ColorIndex = 6

Case Is = "LIKE"

Cells(i, "D").Interior.ColorIndex = 45

Case Is = "LOVE"

Cells(i, "D").Interior.ColorIndex = 46

Case Is = "REALLY LOVE"

Cells(i, "D").Interior.ColorIndex = 3

End Select

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.



Select from the drop downs in column H and the fill color will be set in column D.





Note: Change the text values in the code to your drop down entries. Enter them in all caps as this takes 'case' of the drop down selections.

Answer 2

1. Save the Excel workbook (.xls) file into an add-in (File > SaveAs.....) 2. After you have the saved add-in (.xla), close the Excel workbook (.xls) 3. Double click on the add-in to open it 4. Press Alt+F11 to access the add-in's vba project 5. Lock the vba-project with a password 6. Double-click on the "ThisWorkbook" code module 7. Press F4 to open the Properties window 8. Change the "IsAddin" status to FALSE 9. Return to Excel by Pressing Alt+Q (or close the vbe window) 10. Go to Tools > Share Workbook 11. When the dialogue appears, check the box for: (Allow changes by.....) 12. Press OK to close the Dialogue 13. When prompted to save, Press OK 14. Press Ok to accept that "macros cannot be accessed" 15. You should feel giddy at this point because you just realized what you have been missing right under your nose 16. Verify that the [SHARED] appears in the application title bar 17. Now save the workbook again as an Add-in (File > SaveAs...) overwriting the previous one 18. Close this Excel workbook without saving the changes (you don't need it) 19. Test out your newly saved add-in (open it, access the vbe, try to expand the project window, you should get the new message "Project is Unviewable" 20. Your done

Answer 3

you can do that in the spreadsheet without VB code, using conditional formatting...if you are using Excel 2007