If function & conditional formatting

**spursrule68** · 05-31-2012, 06:00 AM

Really easy question probably, if I want the if function to determine whetehr the cell A2 has content or no content. I.e. if it has a character in it, what do I write in the

'logical test box'?

If characters are present i would like my new cell A13 to turn blue.

How can i achieve this?

**jraj1106** · 05-31-2012, 06:11 AM

Hi

Try this:

Book1.xlsm

**spursrule68** · 05-31-2012, 06:14 AM

hi, thanks for the reply, you could save the file as excel 2000 please as my excel version cannot open it

thanks

**jraj1106** · 05-31-2012, 06:18 AM

This is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A2").Value <> "" Then
        Range("A13").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorLight2
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    Else
        Range("A13").Select
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If
End Sub

**spursrule68** · 05-31-2012, 06:20 AM

Originally Posted by jraj1106

This is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A2").Value <> "" Then
        Range("A13").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorLight2
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    Else
        Range("A13").Select
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If
End Sub

great, i've pasted that in the vba section under worksheet one.

What do i need to do for it to work?

**Fotis1991** · 05-31-2012, 06:14 AM

Hi

In conditional format rules, try

=A1<>""

**spursrule68** · 05-31-2012, 06:18 AM

Hi fotis.

thanks for the reply.

I am pretty much a novice at excel so could you guide me through it step by step?

Thanks mate

**Fotis1991** · 05-31-2012, 06:19 AM

You changed the title in 11:06. I send my answer at 11:04 and deleted immediately..

In conditional format rules, type this formula that i provided you...

Also your request here is a little different than here...

http://www.excelforum.com/excel-prog...-function.html

So, pls don;t send the same question twice!!

It;s duplicate thread..

**spursrule68** · 05-31-2012, 06:21 AM

oh, sorry.

Ignore that thread, let that one rot away. i'll make sure not to create duplicats, although that question is slightly different to this one.

**Fotis1991** · 05-31-2012, 06:25 AM

..I am waiting for a Moderator order..

May i continue answering in this thread??

**spursrule68** · 05-31-2012, 06:43 AM

ok I'll wait.

**Fotis1991** · 05-31-2012, 07:03 AM

As i saw a Moderator, closed your other Thread. So i can continue here.

I apologize for the comfusion, i don't want to be unpolite, but i believe that all of us must Obey to the forum rules.

So in A13, open the Conditional formatting rulew and type the formula. Choose blue color>>Ok.

See the example.

Is this, works for you?

**spursrule68** · 05-31-2012, 07:07 AM

That is superb mate! much appreciated!

Just a quick question. if a web query draws in datafrom a table from a web page daily, but sometimes that web page does not contain the table, will the web query cells overwrite with the invalid section or will the old data remain?

**Fotis1991** · 05-31-2012, 07:18 AM

..Iam not sure if i understand well your question(My English, are not so good), but i think that will these will be overwriten...

But, pls, take another opinion for this...!

**spursrule68** · 05-31-2012, 07:22 AM

thanks anyway

For everyone else:

I have a webquery that references TWO rows when the table is present

When the table is not present, will it only reference one line - the line saying no table is available or will the two lines remain and the data will not be updated?

Thanks

**Fotis1991** · 05-31-2012, 07:25 AM

You are welcome!

My suggestion is to start anew Thread for this question

**spursrule68** · 05-31-2012, 07:45 AM

Originally Posted by Fotis1991

You are welcome!

My suggestion is to start anew Thread for this question

Very very very last question

before I class this as solved.

What would I need to change in terms of the conditional formatting equation so that if any characters are in the cell A2, the colour changes?

**Fotis1991** · 05-31-2012, 07:55 AM

..You mean ..=IF(ISTEXT(A1)>>>Green color...??

=IF(isnumber(a1)>>Blue color...?

**spursrule68** · 05-31-2012, 08:00 AM

fotis - as in

the same conditional formatting formula except can the formula
=A2<>""

be changed so that if anything is written in A2, the cell colour changes

**Fotis1991** · 05-31-2012, 08:08 AM

But this formula, DOES, exactly this...

This means that if you type ANYTHING in A2,then A13 become Blue, or else is Blank...

So...?

**spursrule68** · 05-31-2012, 08:13 AM

Originally Posted by Fotis1991

But this formula, DOES, exactly this...

This means that if you type ANYTHING in A2,then A13 become Blue, or else is Blank...

So...?

Lol yeah you are correct! My bad, sorry for wasting your time

YOu have helped me a lot!

If function & conditional formatting

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

If function & conditional formatting

Re: If function & conditional formatting

Re: If function & conditional formatting

Re: If function & conditional formatting

Re: If function & conditional formatting

Re: If function & conditional formatting

Re: If function & conditional formatting

Re: If function & conditional formatting

Re: If function & conditional formatting

Re: If function & conditional formatting

Re: If function & conditional formatting

Re: If function & conditional formatting

Re: If function & conditional formatting

Re: If function & conditional formatting

Re: If function & conditional formatting

Re: If function & conditional formatting

Re: If function & conditional formatting

Re: If function & conditional formatting

Re: If function & conditional formatting

Re: If function & conditional formatting

Re: If function & conditional formatting

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions