Help with conditional format code

simmo

New Member
Joined
Oct 28, 2002
Messages
36
I am trying to write a macro the which uses conditional formats and looks in a specific column, ignores empty cells, finds the minimum value, and then changes the colour of a cell in a different column but on the same row, here is my attempt up to now, my macro looks in column N for the min value, but its the cell in column C on the same row as the min value i want to colour, the macro then does the same with column O
Sub Macro1()
'
' Macro1 Macro
' Keyboard Shortcut: Ctrl+m
'
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=N1=MIN(N$1:N$120)"
Selection.FormatConditions(1).Interior.ColorIndex = 34
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=O1=MIN(O$1:O$110)"
Selection.FormatConditions(1).Interior.ColorIndex = 40

End With

End Sub
Hope this makes sense
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
The way I understood your post, you want to find the minimum value in the range of N1:N120, and shade the cells in columns C and O of the same row as that minimum value in column N. If that is the case, the following macro does that, tested on XP. If it's not the case, please repost. I wasn't sure what you meant by "the macro then does the same with column O", so I guessed and came up with the following:


Sub MinimumFormat()
Range("C1:C120,O1:O120").Interior.ColorIndex = 0
Dim MinRange As Range
Dim MinVal As Long
Set MinRange = Range("N1:N120")
MinVal = Application.WorksheetFunction.Min(MinRange)
MinRange.Find(What:=MinVal, After:=Range("N1"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
With ActiveCell
.Offset(0, -11).Interior.ColorIndex = 36
.Offset(0, 1).Interior.ColorIndex = 40
End With
End Sub
 

simmo

New Member
Joined
Oct 28, 2002
Messages
36
Hi Tom thanks for your reply, i will try to be more precise. I neen the min value from column N (ignoring empty cells)then instead of changing the colour of that cell, change the colour of a cell in the same row but in column C.
I then have to repeat the code but looking at another column for example look for min value of column O, and change the colour the cell of a cell in the same row but in column E. Hope this explains .
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
Try this then:

Sub FormatMinimum()
Range("C1:C120,E1:E120").Interior.ColorIndex = 0
Dim MinRange1 As Range, MinRange2 As Range
Dim MinVal1 As Long, MinVal2 As Long
Set MinRange1 = Range("N1:N120")
Set MinRange2 = Range("O1:O120")
MinVal1 = Application.WorksheetFunction.Min(MinRange1)
MinRange1.Find(What:=MinVal1, After:=Range("N1"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, -11).Interior.ColorIndex = 34
MinVal2 = Application.WorksheetFunction.Min(MinRange2)
MinRange2.Find(What:=MinVal2, After:=Range("O1"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, -10).Interior.ColorIndex = 40
End Sub
 

simmo

New Member
Joined
Oct 28, 2002
Messages
36

ADVERTISEMENT

Thanks Tom,
I ran your code and it failed, the message was "compile error named argument not found.
in the code it highlit in blue this piece of code "SearchFormat:="
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
You saw that error because the macro was written and tested on XP but you ran it on a previous version. SearchFormat is an argument in Excel XP's Find method that is not supported by versions before XP. All we need to do is take out that SearchFormat argument, which I did. The following revision was tested successfully on XL2K, W98:

Sub FormatMinimum()
Range("C1:C120,E1:E120").Interior.ColorIndex = 0
Dim MinRange1 As Range, MinRange2 As Range
Dim MinVal1 As Long, MinVal2 As Long
Set MinRange1 = Range("N1:N120")
Set MinRange2 = Range("O1:O120")
MinVal1 = Application.WorksheetFunction.Min(MinRange1)
MinRange1.Find(What:=MinVal1, After:=Range("N1"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False).Offset(0, -11).Interior.ColorIndex = 34
MinVal2 = Application.WorksheetFunction.Min(MinRange2)
MinRange2.Find(What:=MinVal2, After:=Range("O1"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False).Offset(0, -10).Interior.ColorIndex = 40
End Sub
 

simmo

New Member
Joined
Oct 28, 2002
Messages
36
Thank a lot Tom the code you gave me worked a treat. Thank again
P.S. The more I learn the more I realise how little I really know.
Cheers
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,462
Members
414,069
Latest member
StudExcel

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