Excel 2010 help: Change text colour by cell contents in a table

[Aotrs Commander]

Okay, after far too much time faffing about with Excel just... not doing what its told, I went "this was supposed to take five minutes and I'm working on my off day, sod it, ask the playground, they will know and that'll be faster than I can struggle with it."

I am doing a printing log for my printer. I want to have some cells in the table I have change colour to red text if in the "fail" column, I put in "Fail" ¹, so I can easily see where something has failed. (Specifically, the "notes" and "model" columns and the "fail" column itself.)

Excel simply doesn't want to do it, unless it expects me to make a conditional formatting rule for EVERY row (which is ludicrous, especially as I don't know how many rows I'll want...)

I have got as far as

"Use a formula to determine which cells to format"

=IF($F$13="Fail",TRUE,FALSE)

Format rules change to red text

Applies to =$N$13

I CANNOT make either selection apply correctly to each row. If I select a range, it will then colour the entire column if Fail is anywhere. It just completely ignores the table [@[column name]] format (something I didn't even know was A Thing until I formatted the data into a table, really just so that I could have the lines alternately shaded).

Help?



¹Or "Y" or "1" or whatver.

[factotum]

I think the problem might be that you're using SFS13 as your cell reference. That will always refer to cell F13 regardless of whereabouts the formula is moved to. What I think you want to do is use F13 there, which will get Excel to use its automatic offset function to update the cell reference correctly as it moves down the column.

[Aotrs Commander]

I think the problem might be that you're using SFS13 as your cell reference. That will always refer to cell F13 regardless of whereabouts the formula is moved to. What I think you want to do is use F13 there, which will get Excel to use its automatic offset function to update the cell reference correctly as it moves down the column.

Tried that myself (one of the first things I tried, actually) - it automatically puts in the $s, so you have to manually delete them. Makes no difference, it doesn't seem to pay any attention to anything else, $ or no. Furthermore Excel auotmatically puts the $ in on the "applies to" box and even if you delete them in the forumla, as soon as you click okay, it puts them right back in.

So there must be more to it than that.

[Manga Shoggoth]

It can be done with a little bit of VBA - I'm having a look at it now.

EDIT: After a little playing...

When I last did something like this, I did the formatting from a subroutine rather than a function (so it checked all the rows in the sheet). The subroutine was triggered from a button press. Worked a treat.

However, it doesn't seem to like doing the same thing from a VBA function. The function actually breaks as soon as it starts doing the format changes (and I have tried the same changes in subroutines and they work there. I'll have a poke around and see if I can get something to work next week.

Failing that, I can code something up to run from a button press. Slighty more clunky, but works.

[Khedrac]

I do this all the time in Excel using conditional formatting (it pleases managers to have test results shown in suitable colours). It looks to me as if you almost have it, but something isn't selected correctly and without more details I cannot be sure what.

My method:
E.g. format the rows in B3 to H200 based on the value in column B.

Step 1 - Highlight the range you want to apply the formatting to.
E.g. B3 to H200

Step 2 (and this is critical) - get the active cell to the top row.
E.g. C3

Step 3 - select Conditional Formatting > New Rule

Step 4 - select Use a formula to determine which cells to format.

Step 5 - In to the formula box type the formula for the currently active cell.
E.g. =$B3="Pass"
(This will format all the cells of the row based on the column B value.)

Step 6 - select Format and set the format you want applied.

Step 7 - select OK.

___

If I had to guess I would predict that you either had the wrong active cell (so all your tests were working offset to what you expected) or you got the $-signs in the formula wrong (or both). Remember, regardless of the range selected the formula is entered is applied to the active cell and extrapolated from there, and if you select regions in a similar method to me that will often initally be a cell on the bottom row...

Personally I would never bother with =IF(condition,TRUE,FALSE) because it is simply extra processing when compared with =condition and Excel will slow down a lot if you start applying a lot of calculations to large areas.

Edit: be warned, once you have got your data sheet right it will look great until people start copy+pasting data around it - at which point the conditional formatting will need resetting as it will get split up into lots of discontinuous chunks.

[Aotrs Commander]

I do this all the time in Excel using conditional formatting (it pleases managers to have test results shown in suitable colours). It looks to me as if you almost have it, but something isn't selected correctly and without more details I cannot be sure what.

My method:
E.g. format the rows in B3 to H200 based on the value in column B.

Step 1 - Highlight the range you want to apply the formatting to.
E.g. B3 to H200

Step 2 (and this is critical) - get the active cell to the top row.
E.g. C3

Step 3 - select Conditional Formatting > New Rule

Step 4 - select Use a formula to determine which cells to format.

Step 5 - In to the formula box type the formula for the currently active cell.
E.g. =$B3="Pass"
(This will format all the cells of the row based on the column B value.)

Step 6 - select Format and set the format you want applied.

Step 7 - select OK.

___

If I had to guess I would predict that you either had the wrong active cell (so all your tests were working offset to what you expected) or you got the $-signs in the formula wrong (or both). Remember, regardless of the range selected the formula is entered is applied to the active cell and extrapolated from there, and if you select regions in a similar method to me that will often initally be a cell on the bottom row...

Personally I would never bother with =IF(condition,TRUE,FALSE) because it is simply extra processing when compared with =condition and Excel will slow down a lot if you start applying a lot of calculations to large areas.

Edit: be warned, once you have got your data sheet right it will look great until people start copy+pasting data around it - at which point the conditional formatting will need resetting as it will get split up into lots of discontinuous chunks.

Aha. After some fracking about with control and making sure I control-select the fail colum last (and thus its top cell was the acvtive cell, since I'm not sure how to change the active cell in a selection aside from using Tab and that not only took ages but I missed and had no idea how to go back one), actually appears to have worked.

Thanks!

Silly me for actually trying to do it the way the interface badly suggested...

(I tend to automatically use If a lot, since it was my major tool in doing all the heavy lifting when I coded the spreadsheet for my Accelerate and Attack ship generator. (It probably make actual coders cry, but it works...))

It can be done with a little bit of VBA - I'm having a look at it now.

EDIT: After a little playing...

When I last did something like this, I did the formatting from a subroutine rather than a function (so it checked all the rows in the sheet). The subroutine was triggered from a button press. Worked a treat.

However, it doesn't seem to like doing the same thing from a VBA function. The function actually breaks as soon as it starts doing the format changes (and I have tried the same changes in subroutines and they work there. I'll have a poke around and see if I can get something to work next week.

Failing that, I can code something up to run from a button press. Slighty more clunky, but works.

I think that might be unecessary, as Khedrac's solution seems to work.

[Manga Shoggoth]

I think that might be unecessary, as Khedrac's solution seems to work.

If nothing else, my employer will be pleased that I'm not doing non-work stuff at work...

[Excession]

=IF($F$13="Fail",TRUE,FALSE)

First, you don't need the if function. =$F$13="Fail" returns a boolean value itself, so you can use that without anything else around it.

Next, you need to go back and edit the conditional formatting rule, and remove the second dollar sign. The row needs to vary with the row being formatted, while the column presumably doesn't need to. You should do that edit while you have all the relevant rows selected, or copy and paste one row out to however many you want afterwards. The final formula will be something like =$F13="Fail". From my experience, the conditional formatting editor may try to add extra stuff in there, which you might need to edit out manually.

This is what my sheet and rule looked like when I tried it:

[Khedrac]

Aha. After some fracking about with control and making sure I control-select the fail colum last (and thus its top cell was the acvtive cell, since I'm not sure how to change the active cell in a selection aside from using Tab and that not only took ages but I missed and had no idea how to go back one), actually appears to have worked.

Thanks!

Silly me for actually trying to do it the way the interface badly suggested...

(I tend to automatically use If a lot, since it was my major tool in doing all the heavy lifting when I coded the spreadsheet for my Accelerate and Attack ship generator. (It probably make actual coders cry, but it works...))

My pleasure. You don't need to be in the control column, it's just that the forumla you type in has to work for the cell you currently have selected and this is usually easier to work out if you are in the first row. If you are feeling confident you can put the forumla in from any cell, just make sure you are referencing the correct control cell(s) for the cell you are in (note: I don't try this because it is too easy to make a mistake and they can be very hard to find).

As for moving around within a selected block of text in addition to [tab] (move right) and [enter] (move left) don't forget [shift] + [tab] (move left) and [shift] + [enter] (move up).