I am getting pretty good with excel formulas but some of the changes to conditional formating do not seem to work as described. I really wish I was better using VBA code. I have read many forums that offer solutions to make this work but I have yet to get mine to work.
I have a basic sheet with picker names that creates a sum of lines picked and an average.
I would like to use conditional formatng that certain pick areas are turned a fill color once it is typed in.
Normally this would be easy but I want the source for the conditional formating be a range
(H149:H159) so that I can change the words in the range without having to adjust the conditional formatting.
This way if the word in B4 is “OR Soft” and it matches any text in the cell range of H149:H159 it formats the cell to fill orange.
I can type the words and the conditional formating works but can not just select the source as a range as it tells me to amke it a formula. I tried may verity of suggested formulas but could not get it to work.
Perhaps if you shared the equation you are trying to use, you would get a little more help. From the picture it seems that you’ve set up your formula incorrectly, as I see an error “Divide by zero”. Perhaps you can try playing around with your cells to avoid dividing by zero, or redefining your formula.
I can’t really help much more without any other info.
Divide by zero can be an error or a by product of incomplete data depending on the circumstance. In this case the formula is AVERAGE and since the SUM column is empty it is divide by zero. I could pretty up the formula with an IFERROR but it is not needed in this instance.
The total form is not much more then what is shown. I can easily change the fill with CF but wanted to base the change on a range of cells instead of 1 word. The range would be H149:H159
I have a book on VB code writing if you’d like. It covers the basics, like generating a pick list or creating if/then check boxes.
You’re going to have to set up a logic column next to your input column, returning a “true/false” scenario referencing each input cell and the text range that you’re matching. Then you can conditionally format individual cells based off the true/false returns by formatting off the word “true” or “false”, depending on the return (and what you wanted to do).
This could be more efficiently done using macros, as if you were to do this for many arguments your spreadsheet would end up being massive. ex: for one cell, you have a text reference range of 10 cells, 10 logic cells, a summation logic/text cell returning an “all true, all false, part true, part false”, and the conditional formatted cell that is your input cell. So we are using 22 cells to format the color of 1 target cell. Imagine if you had to do this for a product catalogue w/ 100 items . . . you’d have 2200 active formula cells.
I have that VB book if you’d like to borrow it. Let me know
