Conditional formatting Question

Lmaonade

New Member
Joined
Jan 5, 2018
Messages
40


How do I set up conditional formatting for all of the cells between I and R to change to Green if the cell is above the target cells (C) and red if it's below. I can't seem to find a way without setting it up for every single row individually. I realise this is probably something simple but I am quite new to this.

Thanks,
Andy
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,096
Office Version
365
Platform
Windows
Select the whole range that you want to apply it to at once, and then write the formula as it applies to the very first cell in your selection.
Be sure to use absolute range references for the parts of the range that you do not want to float/change.

For example, if the first cell in your selection is I2, and you are checking to see if it is above the value in C2, the Conditional Formatting formula would look something like:
Code:
=I2>$C2
By "locking down" the column reference (C), that ensures that all other cells in your range will allows look at column C also.

See here if you are unfamiliar with locking down parts of range references: http://www.cpearson.com/excel/relative.aspx
 

Muhammad_Usman

Active Member
Joined
Dec 9, 2015
Messages
324
Dear Lmaonade! Simple Select Range From I2 : R35 or where you data ends,

>> Go to Conditional Formatting
>> Select Greater Then
>> Select C2 in which there are target Value
>> Type $C2 instead simple C2.
>> You will got you desired Result.
 

Forum statistics

Threads
1,089,493
Messages
5,408,602
Members
403,217
Latest member
dmcmaste

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top