Format Range based on value in Column and Row.

[Trebor200]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2
3
4									1	10	20	30	40	50	60	70	80	90	95	100
5	Col1	Col2	Col3	Col4	Col5	Col6	Col7	Col8	Desc1	Desc2	Desc3	Desc4	Desc5	Desc6	Desc7	Desc8	Desc9	Desc10	Desc11	Desc12
6	123	0030222631	0029670100	50
7	165	0030476402	0029670093	80
8
9
10					Conditional formatting used
11
12
13
14
15
16
17
18
19
20
21
22
23
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J6:J7	Expression	=IF($D6=J$4,TRUE,FALSE)	text	NO
J6:J7	Expression	=IF($D6>J$4,TRUE,FALSE)	text	NO
I7	Expression	=IF($D7=I$4,TRUE,FALSE)	text	NO
I7	Expression	=IF($D7>I$4,TRUE,FALSE)	text	NO
I6,K6:T7	Expression	=IF($D6=I$4,TRUE,FALSE)	text	NO
I6,K6:T7	Expression	=IF($D6>I$4,TRUE,FALSE)	text	NO

Thanks in advance.

 

[Peter_SSs]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
4									1	10	20	30	40	50	60	70	80	90	95	100
5	Col1	Col2	Col3	Col4	Col5	Col6	Col7	Col8	Desc1	Desc2	Desc3	Desc4	Desc5	Desc6	Desc7	Desc8	Desc9	Desc10	Desc11	Desc12
6	123	30222631	29670100	50
7	165	30476402	29670093	80
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I6:T7	Expression	=I$4<$D6	text	NO
I6:T7	Expression	=I$4=$D6	text	NO

 

[Trebor200]

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]

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]

.. 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]

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.

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

 

[Trebor200]

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.

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!

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]

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]

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]

Thanks for updating your profile.