Conditional formatting as you move down a row

1021557

New Member
Joined
Dec 14, 2007
Messages
48
Hi, I have setup a formula that is based on other rows to return a certain value, in effect in cell AW4 it's something like =IF(AM4="",Average,AM4) with Average being a named range. Cell AW5 has the same formula except the values for average move down one; I'm covering range AW4:BE94 with this formula. It all works great but what I want to do is conditionally format the cells in range AW4:BE94 so that if the value in those cells is equal to the average value in its corresponding row then it will be highlighted, if it something other than the average then it's not highlighted. I'm kind of stumped as while it's easy to highlight based on one value this is dynamic as the Average figure is different in each row as you move down the sheet. I've tried putting in different formulas but none seem to work. Any help would be appreciated.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,389
no sure i understand what you are comparing

I want to do is conditionally format the cells in range AW4:BE94 so that if the value in those cells is equal to the average value in its corresponding row then it will be highlighted,
You are taking the average of AW4:BE94
so thats easy
=Average($AW$4:$BE$94)
now we need to compare to the cell you want to reference

can you give a few examples
 

1021557

New Member
Joined
Dec 14, 2007
Messages
48
Hi, Sorry what you propose isn't correct. Just to clarify, suppose I have a range of prices for a particular product in cells A2:I2 but as some suppliers haven't quoted a price for that item then the cell is blank, so just say that cell A2 is blank. Then in cell J2 I calculate the average of A2:I2. I have assigned the range name 'Average' to the averages column, i.e. column J. Following on from that I have a formula in cells K2:S2 such that if there is a value in a cell A2:I2 then it returns that value, if not then it returns the average from J2. I then have this working down a few hundred rows. This all works fine. What I'd really like to do though is where the cells in columns K2:S2 are returning the average as opposed to returning a value from cells A2:I2 I'd like to use conditional formatting to highlight these. Something like: If reading a non-blank cell in A2:I2 then don't highlight but if returning the average value from column J then make colour red. Hope this makes sense. Thanks again.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,389
what formula are you using to test A2 is blank and then enter the average into J2 ?
Then what formula are you using in K2:S2

you can use a very similar formula for conditional formatting
 

1021557

New Member
Joined
Dec 14, 2007
Messages
48
J2 formula is =AVERAGE(A2:I2)
Average or value formula is =IF(A2="",Average,A2) and so forth for each row and column where I'm using the actual or average
 
Last edited:

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,389
Then you can use the formula to show true
=$A2<>""

If A2 is NOT blank - then it will be using A2 value and
you can use that in the other cells for conditional formatting value

The $ keeps the column A as you move from K to S

for 2007, 2010 or 2013 excel version
Conditional Formatting


Highlight applicable range >>

K2:S2



Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:



=$A2<>""



Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

if you want to test
A to K for blank
take off the $
 

Forum statistics

Threads
1,081,991
Messages
5,362,595
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top