Quick nudge in the right direction

matrix26

New Member
Joined
Dec 16, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
I have a report that can be up to 200 rows long.
I want to format columns J through to N (inclusive) based on whether the cell value in J, K, L, M, N is less than it's corresponding value in row E.
Like a fool I recorded a macro where I went through each row individually (all 200) and did the conditional formating required.
But when I ran the macro to test I got the error message that there was too much contained in the macro so it failed to run.

The contents of columns E, J, K, L, M, & N can change with each report dependant upon the devices the report is run on.

I then attempted to create formatting by a new rule but this just highlighted everything in the selected columns.

I want to this to run via a button as I have to run similar reports on different devices at various parts of the day.

What's the best way to go about this?

Thanks all
 

Attachments

  • Capture.JPG
    Capture.JPG
    130 KB · Views: 6

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,537
Office Version
  1. 365
Platform
  1. Windows
based on whether the cell value in J, K, L, M, N is less than it's corresponding value in row E.
What do you mean by that? It looks like the cells you are referring to are text, not numerical.

It would also be helpful if you could give us some small sample data and the expected results (formatted manually) with XL2BB so that testing could be done without having to manually type out loads of data. :)
 

matrix26

New Member
Joined
Dec 16, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
What do you mean by that? It looks like the cells you are referring to are text, not numerical.

It would also be helpful if you could give us some small sample data and the expected results (formatted manually) with XL2BB so that testing could be done without having to manually type out loads of data. :)
Hi Peter,
The data is a mix of alpha-numerical.

Sub formatting()
'
' formatting Macro
'

'
Range("J52:M52").Select
Application.CutCopyMode = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=$E$52"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
 

Attachments

  • Capture.JPG
    Capture.JPG
    36 KB · Views: 6

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,537
Office Version
  1. 365
Platform
  1. Windows
So you want to highlight anything in J:N if it is alphabetically before the value in column E in the same row?
 

matrix26

New Member
Joined
Dec 16, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

So you want to highlight anything in J:N if it is alphabetically before the value in column E in the same row?
Almost,

Not alphabetically but based on the numerical part of the cell value.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,537
Office Version
  1. 365
Platform
  1. Windows
based on the numerical part of the cell value.
There are multiple numerical parts in the cells.!! How would we be expected to know which part or parts to consider?

You may very well know what you want but we have no idea unless you make it very clear. What about a few specific examples and explain exactly how you work out whether values should be coloured or not. In you your last screen shot (again not very useful as we cannot copy from it) you gave one example. You need to give several otherwise we have no idea of what variety might occur within your data.
 

matrix26

New Member
Joined
Dec 16, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

There are multiple numerical parts in the cells.!! How would we be expected to know which part or parts to consider?

You may very well know what you want but we have no idea unless you make it very clear. What about a few specific examples and explain exactly how you work out whether values should be coloured or not. In you your last screen shot (again not very useful as we cannot copy from it) you gave one example. You need to give several otherwise we have no idea of what variety might occur within your data.
I don't mean to cause such frustration

There is no variety of colour.
I'll make it as succinct as possible
I'm trying to highlight anything in columns J, K, L, M, N, O that is lesser in value than it's corresponding entry in column E.
I.E
Column E contains the current software running on the device
Columns J, K, L, M, N contain the names of other software saved to the device.
I want to be able to tell at a glance which software in J, K, L, M, N is older than the version in column E for each device.

Just 1 colour, red, to show the oldest IOS on each device.

I can't be any clearer.

Perhaps what I'm trying to do would be better done in something like python?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,537
Office Version
  1. 365
Platform
  1. Windows
Your last (one) example had
Col E: isr4300-universalk9.16.09.04.SPA.bin
Col J: isr4300-universalk9.16.09.03.SPA.bin

There are clearly two numerical parts in these. From your colouring of the second one I guess, but you haven't spelled it out, that it is the second group of numbers we are comparing.
But what if Col J was
Col J: isr4299-universalk9.16.09.04.SPA.bin
Would that still be "less than Col E because 4299 < 4300 even though the last group of numbers are now equal?

What if Col J was
Col J: isr4300-universalk9.16.12.03.SPA.bin
Would that be < Col E because the final 03 < 04, or
Would that be > Col E because 12 > 09

In your original samples data you had entries like
c800-universalk9-mz.SPA.153-3.M6.bin
These have four numerical parts. How would we know which one or ones to compare?

Perhaps you can start to see why a larger set of sample data and results and a detailed explanation is required for somebody who knows nothing about what all those codes mean?
 

matrix26

New Member
Joined
Dec 16, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Thanks for your time.
I'll work it out
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

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