Forum Discussion
Conditional formatting with adjacent cells
Thank you. I appreciate your attempting to help me.
Let me better explain what I require:
In cell I3, i want to use the conditional format based on the value of J3.
I then want I4 to apply the same formula based on the value of J4. The same for I5 and J5, and so on.
I can use the format painter to apply the formula in the coulumn "I", but it will apply the formula only based on the value of J3.
I hope this provides more clairity of the situation, and I am grateful for the help. -James
Hi James,
I see. The workaround is to use offset, which gives the reference on next column and current row. Not sure what's your formula, could be like
=0.67*OFFSET($J$3,ROW()-3,0,1,1)
and apply it to your entire range. row()-3 returns first row in your range.
I've attempted this solution but with the formula using $J$3 as an absolute reference in the Conditional Formatted formula, how can it be applied to the cells beneath such as J$4$, J$5$, etc?
For example, here is the range of the entire column I wish to format following the formula in cell P4.
In order to apply the same formatting to all cells in the same column, the formula would have to be relative such that the formula would change depending on the cell being formatted. However, I keep getting this error when attempting to use a relative reference.
How do I over come this challenge?
Sorry, I didn't catch which formula you try to use. Please check attached sample for Grade Scale.
I spent the last 48 hours periodically trying to solve this problem and finding that same ineffective formula the original poster referenced, over and over. You appear the be the only person on Earth who knew how to do that, and thank you SO MUCH.
You are welcome. That's not my idea, if you google perhaps you find the post from the person who originally suggested the solution. But I lost the link and don't remember who that was.
Forgot to say, with this you shall apply your rule only to first cell in the range (I3), aplly to next ones by format painter
Works great!! Thank you.
There will be an issue in the future, as this is going to be used as a continous log, when rows are added, that column will need to be "repainted" from the top cell, for the equation to be consistant. -But, that is another conversation, for another day.
Thank you very much!!
James,
You are welcome. When you are ready with new question better if you attach small sample file, will be easier to discuss.
Thank you
