Trying to use nested IF formula to color cells.

[tgriesdorn18]

Hi,

I am trying to use the IF formula or a similar VBA/Macro to color certain cells. We deal with 16 different sand types that come in on rail cars. I want to put in the sand type in a cell; EX: C11 has text "30/50BH" if this statement is true to color cells A11-D11 yellow. If it is not a true statement to check for the next sand type, "20/40BH" and go on from there. If I can get an example of what to do I can build it for the 16 sands we have. The formula I have in mind would be something like =IF("30/50BH",[colorA11:D11,Yellow],[IF("20/40BH",[colorA11:D11, Purple], ....... I'm sure there is a less brute force method of doing this, but my knowledge of programming and excel is limited. I am using the 2010 version of Excel.

Thanks for your help in advance.

Tim

[FDibbins]

I am using the 2010 version of Excel.

Your profile says 2003. Please update your profile as necessary, members tailor questions based on your excel version.

You could, if necessary, do this with regulat Conditional Formatting, you would need to set up 16 rules, 1 for each color

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =C11="30/50BH"

[TMS]

Your profile says 2003, which would be a problem. You need to use Conditional Formatting to colour the cells. In 2003, you can only have 3 conditions. In 2010, you can have lots. If you can't use CF, you'd have to use VBA ... you can't change cell colours with a formula except by using CF.

So, you need to set up 16 conditions and associated colours. Not difficult but tedious.

If you need detailed help, post a sample workbook identifying codes and colours.

Regards, TMS

[tgriesdorn18]

I have updated my profile, thank you for the advice there. Will all 16 rules be able to be simultaneously checked for each cell? I have not played around with CF much before. I've attached a sheet that has two example lines on how the finished product should look. I will research the Conditional Formatting and see what has been come up with here. Thanks for the help.

[FDibbins]

Like I suggested...
1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =$C11="30/50BH"

Repeat 3 and 4 for the other colors/cosed, it will be applied across the range you select

[tgriesdorn18]

I understand your suggestion, but I am having a difficult time getting the color to apply to the range of cells. The test cell is C11, but A11:D11 should all be formatted the same way. What I have found is that only the cell with the "30/50BH" is getting changed to the color specified.

[FDibbins]

I think you missed point 1...

You need to "apply to" the range you need to have CD'd - and not just the single row, then entire range (say, A4:D50)

[tgriesdorn18]

I have selected that entire range, but it will only highlight the cell with the particular information in it. Is there a way for the spreadsheet to use the conditional formatting from a certain cell to apply to a range. In this example the CF would be selected for the entire range of A11:D24, but the rule will only highlight C11 because it's the only cell with "30/50BH" in it. I'm trying to get A11:D11 highlighted with the same color as C11 without them needing to have the "30/50BH" in their cells as well.

[FDibbins]

I'm trying to get A11:D11 highlighted with the same color as C11 without them needing to have the "30/50BH" in their cells as well.

If "30/50BH" is not in any cells, how would it know where to appl the color?

[tgriesdorn18]

The "30/50BH" would be in cell C11. It applies the coloring only to that cell instead of the range A11:D11 like I am wanting it to.

[FDibbins]

OK make up your mind lol...

I'm trying to get A11:D11 highlighted with the same color as C11 without them needing to have the "30/50BH" in their cells as well.

The "30/50BH" would be in cell C11

[tgriesdorn18]

If you look on the example spreadsheet it is formatted how I want it. The only occurrence of "30/50BH" would be in cell C11, but I want the spreadsheet to automatically change the color of the range of cells A11:D11 based on the formatting in C11. Is that possible to do with CF?

[FDibbins]

See if this is what you want

[tgriesdorn18]

That's exactly what I'm looking for. Can you explain the process so I can do the same for the other formats?

[FDibbins]

1. click on any cell in that range
2. go into CF and see what I did, then repeat it for the other rules

I am out of time right now, see how you make out and let me know

[tgriesdorn18]

I have no idea what you did to make it work, but it works and I can edit the formula to work for the rest of what I need. Thanks a bunch, and I'd love an explanation on how it works whenever you have some time. +Rep and Problem Solved.

[FDibbins]

Thanks for the feedback

When I create a CF rule, I often create it IN the worksheet, right next to where it will be applied, I find it easier to fiddle and test the formula there than in the CF window. I create the formula so that it returns TRUE or FALSE (or an answer or an error - which equates to TRUE/FALSE for CF). Keep in mind how you would need to use absoluting ($) so the formula adjusts as you need it when copied down and/or across

Once I have the formula worked out the way I need it, I copy the formula (from the formula bar, not the cell),
then highlight the range I need to apply it to...in your case, A11:D24
Select New Rule
Because you already have the range highlighted, excel will use that.
Select Use Formula, and paste the formula into the formula bar
Select the format color you want and click OK until you get back to the 1st CF window...test what you have so far by clicking APPLY

Once that is working, dont exit CF, just add new rule/use formula, paste the formula and adjust where needed (note, you cannot arrow-left/right in CF, you need to use the mouse which can be tricky)

rinse and repeat

Hope that helps?

[TMS]

note, you cannot arrow-left/right in CF, you need to use the mouse

When the formula box is selected, press F2 to go into Edit mode. Then you can use the cursor to move back and forth in the formula ... just like in Edit mode in a cell.

Regards, TMS

[FDibbins]

Well I'll be a monkey's uncle, thanks for that Trevor, you could have saved me tons of frustration if you had told me that before lol

Thanks

[TMS]

Well I'll be a monkey's uncle, thanks for that Trevor, you could have saved me tons of frustration if you had told me that before lol

I did, two years ago

http://www.excelforum.com/excel-tips...ormatting.html


Regards, TMS

[FDibbins]

hey I cant remember things from 2 weeks ago, let alone 2 years lol