Excel 2010 Conditional Formatting Columns at a time 2

[PWD]

Good morning. Is it possible to set Conditional Formatting for whole Columns - hundreds of rows - based on whether the last character in each cell <> "."?

I can format an individual cell

=(RIGHT(I1,1)<> ".")

but I've just got a mental block on how to apply this to a range such as "I:I". Can it be done? I don't think so. So would I have to run code down each cell?

Many thanks,
D€$

 

[GlennUK]

Be careful of applying Conditional Formatting to entire columns ... it can take a lot of resources. But you can if you want to ( nothing stopping you ). Select the column I before applying the conditional formatting ( making sure that I1 is the active cell ), using your formula, and that's it.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

hi,

Formatting for whole Columns

I agree with Glenn.

With Excel 2007⁺, your table can and SHOULD be a Structured Table, using Insert > Tables > Table.

When you apply a CF to a column in your Structured Table, the CF will expand/contract as the number of rows change in your table.

Structured Tables had all sorts of neat and useful benefits not otherwise available.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Hi Glenn. Trouble is what I want to do is evaluate each cell and highlight it if it doesn't end with ".".

I recorded a macro and it produced the following code:-

Range("J1").Select

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(RIGHT($J$1,1) <> ""."")"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

Normally in a similar situation I'd do something like this; determine the last row with data "EndRow", create a loop

For R = 1 to EndRow

MyAddress = Cells(R,9).Address

MyAddressValue = Right(Range(MyAddress).Value, 1)

If MyAddressValue <> "." Then
Range(MyAddress).Interior.Color = 65535
End If

Next R

but it seems like I can't use variables inside the
Formula1:="=(RIGHT($J$1,1) <> ""."")"
formula.

Des.

 

[PWD]

Hi Skip, I'd gone to lunch halfway through typing my response. Basically this is a workbook we receive from our customer and occasionally one or more of the cells has incomplete data - no rhyme nor reason, it may have 900+ characters just not everything that should be there. One of my colleagues, who checks these things when they arrive, suggested highlighting cells that didn't end in ".". I believe that the original data is cleaned by our customer so each comment (cell) in various columns ends in ".". Therefore if any of them don't, there must be something missing. I'm not entirely convinced but I was willing to give it a go.

So, that's the problem.

Many thanks,

D€$

 

[SkipVought]

Heavens NO!!!

SELECT the range you want to CF.

OPEN the CF Wizard.

ENTER your format criteria. If you are using cell references, make the references relative to the TOP ROW of the SELECTION.

NO VBA REQUIRED OR DESIRED!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Sorry Glenn, I didn't entirely get what you were saying. Doh!

I just couldn't see how

 =RIGHT(I1,1)<>"."

would apply to ANY cell in Column "I". But it does! "Thou art a witch!"

Skip, to be honest the CF process doesn't appear, to me, to be uber friendly but I seem to have got there.

Many thanks to you both.
D€$

 

[SkipVought]

As I stated, the [red]REFERENCE[/red] in the CF criteria formula...
[tt]
=RIGHT([red]I1[/red],1)<>"."
[/tt]
must refer to the [red]FIRST ROW OF THE SELECTION[/red]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Just modified to exclude blank cells

 =and(RIGHT(I1,1)<>".",len(I1)>0)

D€$