Help with conditional format code

[simmo]

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

 

[Tom Urtis]

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]

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]

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]

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]

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]

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