Format Range based on value in Column and Row.

Trebor200

Board Regular
Joined
Apr 21, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I was wondering if someone could help with formatting (colour) cell based on a value being greater than and also equal to.

Data can be many rows down and across. start of range for formatting will be I6
If value in column D is greater than value in row 4 then green else if value in D is equal then yellow else nothing.

I have added conditional formatting to show what i need.

Format based on value.xlsx
ABCDEFGHIJKLMNOPQRST
1
2
3
4110203040506070809095100
5Col1Col2Col3Col4Col5Col6Col7Col8Desc1Desc2Desc3Desc4Desc5Desc6Desc7Desc8Desc9Desc10Desc11Desc12
61230030222631002967010050
71650030476402002967009380
8
9
10Conditional formatting used
11
12
13
14
15
16
17
18
19
20
21
22
23
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J6:J7Expression=IF($D6=J$4,TRUE,FALSE)textNO
J6:J7Expression=IF($D6>J$4,TRUE,FALSE)textNO
I7Expression=IF($D7=I$4,TRUE,FALSE)textNO
I7Expression=IF($D7>I$4,TRUE,FALSE)textNO
I6,K6:T7Expression=IF($D6=I$4,TRUE,FALSE)textNO
I6,K6:T7Expression=IF($D6>I$4,TRUE,FALSE)textNO



Thanks in advance.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,242
Office Version
  1. 365
Platform
  1. Windows
You don't need IF(condition,TRUE,FALSE) in conditional formatting, just the condition. If that condition is true, the formatting will be applied.

If from I6 to T(last row) is selected and this CF applied, does it do what you want? (Note that it is basically the CF you have already applied only with IF removed and made uniform and general over the whole range.)

21 07 15.xlsm
ABCDEFGHIJKLMNOPQRST
4110203040506070809095100
5Col1Col2Col3Col4Col5Col6Col7Col8Desc1Desc2Desc3Desc4Desc5Desc6Desc7Desc8Desc9Desc10Desc11Desc12
6123302226312967010050
7165304764022967009380
CF
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I6:T7Expression=I$4<$D6textNO
I6:T7Expression=I$4=$D6textNO
 

Trebor200

Board Regular
Joined
Apr 21, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows
Hi Thank you for Reply!

Just realized I was not clear and missed the VBA requirement, I want to format using VBA... i have code that builds the report but struggling on defining the range then applying the format on the cell.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,242
Office Version
  1. 365
Platform
  1. Windows
Just realized I was not clear and missed the VBA requirement, I want to format using VBA
.. but could the VBA simply apply the conditional formatting to the required range?
 

Trebor200

Board Regular
Joined
Apr 21, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

.. but could the VBA simply apply the conditional formatting to the required range?
Yes, i just need to figure that out... have the VBA to identify the range... just need to apply... just reading up.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,242
Office Version
  1. 365
Platform
  1. Windows
have the VBA to identify the range... just need to apply..
See if this is any help. I have manually set a range but you will use the range you have identified with your vba.
I have assumed that what is in row 4 above will always be the second row above the relevant range to be formatted and that the 'Col4' values will always be 5 columns to the left of the formatted area.

VBA Code:
Sub Apply_CF()
  Dim Rng As Range
  
  Set Rng = Range("I6:T7")
  With Rng
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=" & .Cells(-1, 1).Address(1, 0) & "<" & .Cells(1, -4).Address(0, 1)
    .FormatConditions(1).Interior.Color = 5296274
    .FormatConditions.Add Type:=xlExpression, Formula1:="=" & .Cells(-1, 1).Address(1, 0) & "=" & .Cells(1, -4).Address(0, 1)
    .FormatConditions(2).Interior.Color = 65535
  End With
End Sub
 
Solution

Trebor200

Board Regular
Joined
Apr 21, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Thanks f
See if this is any help. I have manually set a range but you will use the range you have identified with your vba.
I have assumed that what is in row 4 above will always be the second row above the relevant range to be formatted and that the 'Col4' values will always be 5 columns to the left of the formatted area.

VBA Code:
Sub Apply_CF()
  Dim Rng As Range
 
  Set Rng = Range("I6:T7")
  With Rng
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=" & .Cells(-1, 1).Address(1, 0) & "<" & .Cells(1, -4).Address(0, 1)
    .FormatConditions(1).Interior.Color = 5296274
    .FormatConditions.Add Type:=xlExpression, Formula1:="=" & .Cells(-1, 1).Address(1, 0) & "=" & .Cells(1, -4).Address(0, 1)
    .FormatConditions(2).Interior.Color = 65535
  End With
End Sub

Thanks for the reply, i managed to do this late yesterday using the below... thanks for you input it has helped me!

VBA Code:
Sub FormatData()
Dim CC As Long, RC As Long
    CC = Cells(5, Columns.Count).End(xlToLeft).Column
    RC = Range("D" & Rows.Count).End(xlUp).Row
    Set rng = Range(Cells(6, 9), Cells(RC, CC))
    
    rng.Select
    
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=I$4<$D6"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=I$4=$D6"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,242
Office Version
  1. 365
Platform
  1. Windows
Glad you have it sorted. Thanks for letting us know. :)

Note, however, that you generally do not need to actually select a range to work with it and in many cases selecting can slow your code considerably. See how my code uses "With Rng" and no selection at all is made.

BTW, I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 

Trebor200

Board Regular
Joined
Apr 21, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows
Glad you have it sorted. Thanks for letting us know. :)

Note, however, that you generally do not need to actually select a range to work with it and in many cases selecting can slow your code considerably. See how my code uses "With Rng" and no selection at all is made.

BTW, I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
Noted: Thanks, ill take a look, still learning.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,242
Office Version
  1. 365
Platform
  1. Windows
Thanks for updating your profile. (y)
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,586
Messages
5,765,298
Members
425,271
Latest member
kristyfinn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top