Recognise a cell containing conditional formatting

[Whisperer14]

I have a workbook containing 37 worksheets each with 100 active rows that require conditional formatting.

Each worksheet has between 59 and 153 active columns and there is a mixture of conditional formatting required on each sheet (3 types).

Two of the types will be small in total columns whilst the third contains better than 80% of the cells. Regrettably the formatting is not contiguous; it is not as easy as columns A-B on Type 1 and C-D on Type 2 with the remainder on Type 3, on the other hand there are no holes in the formatting in that any one column contains only the one type of conditional formatting.

Again with the spread of column numbers (59-153), I can not readily automate it with VBA UNLESS....

If I can fill the cells with Types 1 and 2 and then fill the remainder with Type 3 then I can automate it BUT

Q. Is there a way to interrogate a range for the existence of conditional formatting?



Many thanks for a solution or attempt at such.

A Happy, Healthy and Prosperous New Year to all

 

[Tom Urtis]

Q. Is there a way to interrogate a range for the existence of conditional formatting?

Yes, by selecting the range of interest (or entire sheet using Ctrl+A or clicking on the Select all button above row 1 and to the left of column A), and clicking Edit > Go To > Special > Conditional formats > OK. Then you can manipulate that non-contiguous selected range at one time.

 

[Whisperer14]

Tom,

Thanks for the response but it shows all of the conditional formatting at one time and does not differentiate between the types required.

Perhaps a simplified example might serve to explain better

In Columns A, C and E I will have one type of conditional formatting that I can put in by code. Columns D and G have another type of conditional formatting.

What I need to do is to apply the third type of formatting to columns B, F, H et seq.

In pseudo code I was thinking along the lines of

Sub ThirdType
For Col = 1 to Columns.Count
If there is no conditional formatting in this Col then
Apply third type
Next Col
End Sub

ray:

 

[Tom Urtis]

I don't know what you mean by the "third type" - - maybe that means conditional formatting with all 3 conditions set, or maybe it means a certain conditional format unlike either of the other two. In any case, try using the macro recorder for what you want to do, and apply the code for your conditions to the cells and columns you want them to applied to. If you get stuck or I am totally offf base with my understanding (or lack thereof) of your objective, please post back so someone can assist.

 

[Whisperer14]

What I was originally attempting was to manually (or use VBA) apply 2 distinctly different types of conditional formatting to part columns. I was then hoping to loop through each column in the worksheet and check whether a column had any form of conditional formatting, if it had neither of the earlier applied cf then I would apply the third distict type of conditional formatting.

Having had the opportunity to place it on the forum I have realised that I can use the alternative approach of applying the 3rd type of conditional formatting to the whole sheet and then selectively overwriting those columns that require Type 1 or 2 formatting either manually or with VBA.

Thanks Tom, I am sure that there is a way but at the moment I am stumped.