macro for formatting columns if values are greater than or equal to reference cells

[traxdmb34]

Hi all, I'm still in the beginning stages of learning all the fun stuff that can be done with macros. I'm working on some formatting stuff to make my life easier and the last bit I need to figure out is how to bold and/or highlight cells in a column if they are great than or equal to values in two different rows in the same column. My reference values will always be in rows 4 and 5 and start in column J, but the number of columns will vary based on how many results I need to include on a given table. The screenshot shows an example of formatting I did manually in column J, just to show that values over 1 need to be bold and values over 23 need the cell shaded (color doesn't matter). Nothing needs to be done if the cell in rows 4 or 5 is blank. Formatting will need to be repeated every other column to the end of the table.

Can someone point me in the right direction, please? Thanks in advance!

 

[Skyybot]

If your values that need to be formatted are always in Column J, then I think simple Conditional Formatting would be easier than a Macro...?

 

[Peter_SSs]

If your values that need to be formatted are always in Column J, then I think simple Conditional Formatting would be easier than a Macro...?

Perhaps you missed these couple of things?

Formatting will need to be repeated every other column to the end of the table

the number of columns will vary

@traxdmb34
This does use conditional formatting but allows for the variations you described. It assumes no conditional formatting already in the relevant columns.

Sub Test()
  Dim myCols As Range
  Dim lastcol As Long, lastrow As Long, c As Long
  
  lastcol = Cells(4, Columns.Count).End(xlToLeft).Column
  lastrow = Columns("J").Resize(, lastcol - 9).Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  Set myCols = Range("J6:J" & lastrow)
  For c = 12 To lastcol Step 2
    Set myCols = Union(myCols, Intersect(myCols.EntireRow, Columns(c)))
  Next c
  With myCols
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(J$4<>"""",J6>J$4)"
    .FormatConditions(1).Font.Bold = True
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(J$5<>"""",J6>J$5)"
    .FormatConditions(2).SetFirstPriority
    .FormatConditions(1).Interior.Color = vbGreen
    .FormatConditions(1).StopIfTrue = False
  End With
End Sub

My sample data and results:

traxdmb34.xlsm
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
4	1		600		700		1000				70		100		500
5	23		3330000		700		7800				690
6	238				306				600		4290
7	57.1				214
8	12.2				800		6000
9	172
10	177				2
11	7.99				3
Sheet3

 

[traxdmb34]

Perhaps you missed these couple of things?



@traxdmb34
This does use conditional formatting but allows for the variations you described. It assumes no conditional formatting already in the relevant columns.

Sub Test()
  Dim myCols As Range
  Dim lastcol As Long, lastrow As Long, c As Long
 
  lastcol = Cells(4, Columns.Count).End(xlToLeft).Column
  lastrow = Columns("J").Resize(, lastcol - 9).Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  Set myCols = Range("J6:J" & lastrow)
  For c = 12 To lastcol Step 2
    Set myCols = Union(myCols, Intersect(myCols.EntireRow, Columns(c)))
  Next c
  With myCols
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(J$4<>"""",J6>J$4)"
    .FormatConditions(1).Font.Bold = True
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(J$5<>"""",J6>J$5)"
    .FormatConditions(2).SetFirstPriority
    .FormatConditions(1).Interior.Color = vbGreen
    .FormatConditions(1).StopIfTrue = False
  End With
End Sub

My sample data and results:

traxdmb34.xlsm
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
4	1		600		700		1000				70		100		500
5	23		3330000		700		7800				690
6	238				306				600		4290
7	57.1				214
8	12.2				800		6000
9	172
10	177				2
11	7.99				3
Sheet3

Thank you so much! I tested this on my table and it's a great help! I have cells in the table that are not numeric values (ND (non-detect) and -- if no result to report); it's bolding/highlighting those, so I'll have to adjust it a bit, but I really appreciate your help!! Thanks again!

 

[Skyybot]

Good catch @Peter_SSs . I did miss those statements. Your solution is elegant.

 

[Peter_SSs]

I have cells in the table that are not numeric values

Yes, I had seen that but then forgot to account for it, sorry!
For the future, if you can provide your sample data with XL2BB then it is less likely things like that will happen since we can test with your actual sample data rather than some briefly-typed (& bad) samples of our own.

Sub Test_v2()
  Dim myCols As Range
  Dim lastcol As Long, lastrow As Long, c As Long
 
  lastcol = Cells(4, Columns.Count).End(xlToLeft).Column
  lastrow = Columns("J").Resize(, lastcol - 9).Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  Set myCols = Range("J6:J" & lastrow)
  For c = 12 To lastcol Step 2
    Set myCols = Union(myCols, Intersect(myCols.EntireRow, Columns(c)))
  Next c
  With myCols
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(J$4<>"""",J6>J$4,ISNUMBER(J6))"
    .FormatConditions(1).Font.Bold = True
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(J$5<>"""",J6>J$5,ISNUMBER(J6))"
    .FormatConditions(2).SetFirstPriority
    .FormatConditions(1).Interior.Color = vbGreen
    .FormatConditions(1).StopIfTrue = False
  End With
End Sub

 

[traxdmb34]

Yes, I had seen that but then forgot to account for it, sorry!
For the future, if you can provide your sample data with XL2BB then it is less likely things like that will happen since we can test with your actual sample data rather than some briefly-typed (& bad) samples of our own.

Sub Test_v2()
  Dim myCols As Range
  Dim lastcol As Long, lastrow As Long, c As Long
 
  lastcol = Cells(4, Columns.Count).End(xlToLeft).Column
  lastrow = Columns("J").Resize(, lastcol - 9).Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  Set myCols = Range("J6:J" & lastrow)
  For c = 12 To lastcol Step 2
    Set myCols = Union(myCols, Intersect(myCols.EntireRow, Columns(c)))
  Next c
  With myCols
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(J$4<>"""",J6>J$4,ISNUMBER(J6))"
    .FormatConditions(1).Font.Bold = True
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(J$5<>"""",J6>J$5,ISNUMBER(J6))"
    .FormatConditions(2).SetFirstPriority
    .FormatConditions(1).Interior.Color = vbGreen
    .FormatConditions(1).StopIfTrue = False
  End With
End Sub

Amazing! Having zero background with macros until very recently, this all seems like some sort of crazy magic to me. The technical understanding is coming...slowly.

Thanks again for your help!

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.

Having zero background with macros until very recently, this all seems like some sort of crazy magic to me. The technical understanding is coming...slowly.

Stick with it & it will come.