Find text across row 1 and highlight those columns

[Bret1]

Is there a way I can search a selected area for text containing ":50" in row 1 and have it highlight each full column that contains it so I can then delete all those columns and shift to the left?

 

[DanteAmor]

Create conditional formatting.

- First select the columns, for example, columns A to E.
- On the Home tab, in the Styles group, click Conditional formatting > New Rule
- In the New Formatting Rule window, select Use a formula to determine which cells to format
- Enter the formula in the corresponding box.

=FIND(":50",A$1)

(Change the letter A to the letter of the first column you selected, that is, if you selected columns F to M, then change A to F. In my example I am selecting columns A to E.

- Click the Format… button to choose your custom format.

- Press OK twice.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Do you really need the whole column Highlighted since you are going to delete it anyway? Wouldn't just highlighting the cell in row 1 be sufficient?

Are those values with :50 in row 1 text values or time values (numbers)?

 

[Bret1]

Create conditional formatting.

- First select the columns, for example, columns A to E.
- On the Home tab, in the Styles group, click Conditional formatting > New Rule
- In the New Formatting Rule window, select Use a formula to determine which cells to format
- Enter the formula in the corresponding box.

=FIND(":50",A$1)

(Change the letter A to the letter of the first column you selected, that is, if you selected columns F to M, then change A to F. In my example I am selecting columns A to E.

- Click the Format… button to choose your custom format.

- Press OK twice.

Thanks!

 

[Peter_SSs]

Thanks!

Just a word of warning. If you use that whole column approach your sheet performance could be impacted. Conditional formatting is volatile (in fact super-volatile as described here)
Suppose the you applied that CF to say 12 columns, 2 of which are then deleted because they are highlighted. That means 10 columns containing almost 10.5 million super-volatile cells remain, doing absolutely nothing for you other than continuously using calculation resources.

So at least if you use that approach, I suggest after deleting the unwanted columns remove the conditional formatting from the remaining columns.

This is why I questioned highlighting entire columns. Just highlighting the :50 cells in row 1 would do the same job of identifying the columns to delete and if the CF remained in just 10 single cells in row 1 instead of over 10 million cells, there would be no impact at all.

Also, please update your version details as requested above.

 

[Bret1]

Create conditional formatting.

- First select the columns, for example, columns A to E.
- On the Home tab, in the Styles group, click Conditional formatting > New Rule
- In the New Formatting Rule window, select Use a formula to determine which cells to format
- Enter the formula in the corresponding box.

=FIND(":50",A$1)

(Change the letter A to the letter of the first column you selected, that is, if you selected columns F to M, then change A to F. In my example I am selecting columns A to E.

- Click the Format… button to choose your custom format.

- Press OK twice.

Thanks for your suggestion! That was a great idea and I think that would have worked, but I don't think I described what I want to do correctly. I don't want to "highlight" the columns, I want it to "select" the columns so I can then manually right click and delete them. I have a ton of data that I want to reduce. I've collected data every 10 minutes every day for over a year. Each column is time and date stamped across row 1. The actual time is copied over as text for each column of data. I want to delete all the columns except on the ":00" and ":30".... So, I want it to select all the columns that have ":50" within row 1, then delete all of those columns (and shift to left to close the gaps), then do the same for ":40", ":20", ":10". I can easily select the columns all manually, but that is a lot of columns to select and will take a lot of time. Currently all the data columns range from column X to MTB with x:00, x:10, x:20, x:30, x:40, x:50 for 8 hours each day. I want to delete all but the x:00 and x:30 for each hour each day for the past year+.

 

[DanteAmor]

Run this macro to select columns with :10, :20, :40, :50, that is, all except :00 and :30

Sub selectcolumns()
  Dim j As Long
  Dim rng As Range
  For j = 1 To Cells(1, Columns.Count).End(1).Column
    If Not (Cells(1, j).Value Like "*:00*" Or Cells(1, j).Value Like "*:30*") Then
      If rng Is Nothing Then Set rng = Cells(1, j) Else Set rng = Union(rng, Cells(1, j))
    End If
  Next
  If Not rng Is Nothing Then rng.EntireColumn.Select
End Sub

 

[Bret1]

Another thing..... The values in row 1 are formatted as "Time" Values", not "Numbers"....... example: "9/20/2023 11:00:09 AM" shown in cell as: "11:00:09 AM"

 

[DanteAmor]

The actual time is copied over as text for each column of data.

The values in row 1 are formatted as "Time" Values", not "Numbers"....... example: "9/20/2023 11:00:09 AM" shown in cell as: "11:00:09 AM"

I'm not understanding, are they text or are they Time Values?

 

[DanteAmor]

In any case Try this macro:

Sub selectcolumns()
  Dim j As Long
  Dim rng As Range
  For j = 1 To Cells(1, Columns.Count).End(1).Column
    If Not (Cells(1, j).Text Like "*:00*" Or Cells(1, j).Text Like "*:30*") Then
      If rng Is Nothing Then Set rng = Cells(1, j) Else Set rng = Union(rng, Cells(1, j))
    End If
  Next
  If Not rng Is Nothing Then rng.EntireColumn.Select
End Sub