PDA

View Full Version : Color Backgrounds for Independant Cells



Steverz
08-05-2008, 01:57 PM
Microsoft Excel 2000: I am trying to build a small "question test" program for the guys in our sports program. In a nutshell, when a person reads a question, they then type a T (for true) or F (for false) in a cell. If they answer the question correctly, the background for that answer cell would turn green and if they answered wrong that one cell would turn red.

The Layout: Column A would be labeled No. (for Number). Column B would be labeled Questions. Column C would be labeled Answer and is where the person would type in a T or F. There would be alot of questions where the cells in Column C would either be green or red after the User types in their T or F answer.

I think this would have to be a Macro but I am not sure. I haven't done a macro since 1998 or Visual Basic 6 since 2001, so being rusty at this is an understatement.

I would appreciate alot of guidance on this project. Thank You.:think:

Simon Lloyd
08-05-2008, 02:04 PM
Just Highlight column C then click Format on the menubar then conditional formatting, you want the Cell Value is Equal To in the next box just type T with no " marks then choose your colour, now click the add button to add a condition and do the same for F.

Steverz
08-05-2008, 02:19 PM
S. Lloyd: Thanks. It works if I add three conditions for each cell. I need white when the program opens, green for true and red for false. I played with it and got it to work by adding the third condition to your two conditions. Thanks again for the great and quick help.

Simon Lloyd
08-05-2008, 02:32 PM
They will stay coloured unless the T or F is removed!

If you mean reload the page by navigating away from it and then back then you can use this:

Private Sub Worksheet_Activate()
Me.Columns(3).ClearContents
End Sub
just right click on the worksheet tab choose View Code and paste the above in, now when you move away from the worksheet and then back all the answers from column C will be removed!

dalea
08-06-2008, 08:20 AM
Look at the attached workbook. Protection is turned on the first sheet but no password has been entered so just use tools, protection, unprotect sheet to remove it to look at the conditional formatting. Data Validation is in effect for the "Answer" column. The "Answers" sheet is where you enter the correct responses to the questions. Of course you will need to "Hide" this worksheet before you make the workbook available to your colleagues.

I see a non-VBA solution as more appopriate to your situation since you would probably be creating new tests daily, weekly, etc. and thus changing the sheet should be quick and convenient. If you want to increase the security, you could use a short VBA macro to xlHide the worksheet so that it could only be made visible by someone with VBA skills.

The solution utilizes "VBLOOKUP", "Condional Formatting" and "Data Validation".