Excel help - if then

[craigjm]

I have a spreadsheet where the user is answering questions with 5 columns being the Likert scale from strongly agree to strongly disagree, I want to colour the cells in another column further along based on the answers to the questions so the cell goes green if they answer agree, amber if neither and red if disagree for example.

Im not sure I can do that with conditional formatting, will it require a macro?

[craigjm]

Sporky said:

I'm not sure Excel is the best tool for this, but as with many things, it may be the least bad tool you've got...

Thats exactly it. If it works for what we need it for after a proof of concept we may be able to shift it to something more suitable

Thanks for the explanation ill give it a go. Thanks everyone ill come back if I get stuck

[craigjm]

Sporky said:

I would put a Group Box (from the Developer Tab, Insert, Form Controls) across all five responses, then five Option Buttons (again from the Developer Tab, Insert, Form Controls), with the option buttons all pointed at the cell in which you want to display the result.

I am going to assume that column A contains the question, then B-F are strongly disagree to strongly agree, then G has the colour code, like this (but line things up nicely):



Select G2, hit Conditional Formatting, New Rule, Use a formula, and then you'll need to do this three times:

First one - formula is "=G2<3", and hit the format button, set the fill to red
Second one - formula is "=G2=3", format button, fill to amber (or maybe yellow)
Third one - formula is "=g2>3", format button, fill to green

That should look like this:



You'll then just need to copy that all down your questions. I'm not sure Excel is the best tool for this, but as with many things, it may be the least bad tool you've got...

Hey Sporky I have now sorted it using your solution, Thanks. Was just wondering if you know of a way of hiding the border around each question group? if you select it and then delete it the buttons dont function correctly as they are no longer in a group. I cant seem to colour the black line white or anything though like you would with normal formatting to hide it. Any ideas?

[craigjm]

Thanks. I think I have found how, on the Mac version of Excel at least



Go into shape format then selection pane and then hide the item

[craigjm]

Thanks for your help

[craigjm]

Its good enough for me to prove my concept and then we can get our coding team to make it into an app so it wont be kept in Excel for long just with this current client. Good to see that Excel can do it though