=FIND(":50",A$1)
Thanks!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.
(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.Excel Formula:=FIND(":50",A$1)
- Click the Format… button to choose your custom format.
- Press OK twice.
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)Thanks!
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+.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.
(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.Excel Formula:=FIND(":50",A$1)
- Click the Format… button to choose your custom format.
- Press OK twice.
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
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"
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