Conditional Formatting Problem

[howard]

I have a value in C22. I would like to highlight the items in Red in F22:K22 using CF where the values are < than in C22


Your assistance in this regard is most appreciated

 

[Vbagreenhorn1]

=f22<$c$22

 

[Marcílio_Lobão]

howard, Good morning.

Select F22:K22

Conditional Format Menu --> Using Formula

Type: =F22<$C$22

Set format fill color to RED

OK

Is that what you want?
I hope it helps.

 

[mandukes]

I don't think you can make it work this through conditional formatting , but you can achieve it through Vba

Here is the code,

Sub highlight()

'Tested and working.. 


Dim LastRow As Long
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
Worksheets("[COLOR=#ff0000]Sheet1[/COLOR]").Activate   [COLOR=#ff8c00]'[/COLOR][COLOR=#0000ff] your worksheet name goes here [/COLOR][COLOR=#ff8c00][/COLOR]


For i = 1 To LastRow + 1


If Range("CF" & i).Value < Range("C" & i).Value Then
For j = 1 To 6
Cells(i, 5 + j).Select
If Cells(i, 5 + j) = Range("CF" & i).Value Then
Cells(i, 5 + j).Interior.ColorIndex = 3
End If
Next j
End If
Next i


End Sub

 

[howard]

Thanks for you help, most appreciated

 

[howard]

Thanks for the help

Kindly amend your code to incorporate the following


1) I have benchmark figures in Col C from row 11 owwards
2) I have values or % in Col D to K
3) Where the values or %'s < than the benchmark, these must be highlighted in red


I have tried amending your code, but could not come right


Kindly amend taking into account the above

 

[mandukes]

Thanks for the help

Kindly amend your code to incorporate the following


1) I have benchmark figures in Col C from row 11 owwards
2) I have values or % in Col D to K
3) Where the values or %'s < than the benchmark, these must be highlighted in red


I have tried amending your code, but could not come right


Kindly amend taking into account the above

' there you go mate ...

'[URL="https://www.mrexcel.com/forum/members/howard.html"]howard[/URL][COLOR=#574123] 
[/COLOR]
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
Worksheets("Sheet1").Activate   ' your worksheet name goes here
Cells.Interior.ColorIndex = 0
For i = 11 To LastRow + 1
If Range("CF" & i).Value < Range("C" & i).Value Then
For j = 1 To 8
If Cells(i, 3 + j).Value < Range("CF" & i).Value Then
Cells(i, 3 + j).Interior.ColorIndex = 3
End If
Next j
End If
Next i

 

[Marcílio_Lobão]

howard, Good evening.

Here is the example of my modest contribution to your question.

https://www.sendspace.com/file/ml1sjs

Using the same process of Conditional Format that I told you before.

I always prefer the simplest solutions.

I hope it helps.

 

[howard]

Thanks for the amended Code


When running the macro, this is what the sample data looks like

Excel 2012
	C	D	E	F	G	H	I	J	K	L
10	Benchmark
11	3.80%	3,4%	3.90%	3,0%	4.25%	1,9%	-2.50%	3,7%	2,1%
12	14,6%	14,2%	13.75%	12,3%	15.80%	16.20%	16.25%	14.10%	14,3%
13
Sheet1

This is what it should look like

Excel 2012
	C	D	E	F	G	H	I	J	K	L
10	Benchmark
11	3.80%	3,4%	3.90%	3,0%	4.25%	1,9%	-2.50%	3,7%	2,1%
12	14,6%	14,2%	13.75%	12,3%	15.80%	16.20%	16.25%	14.10%	14,3%
13
Sheet1

Kindly amend code to so that it highlights the items in red in Cols D:K that are less than those on the same row in Col C

 

[howard]

Thanks Marcilio


CF works perfectly