someone plz help , :)

mercmannick

Well-known Member
Joined
Jan 21, 2005
Messages
730
:oops: :oops:
sorry but i am tryin every way i know how.........

i have a worksheet,
that i would like a formula to look at contents in one cell (all text by the way),and if blank look at next cell to the right and if tht is blank format the entire row red, if there is data in the first cell or second cell to leave as is .


also i would like formula to b able to look at cell and if a zero in there then to highlight entire row green,

plz give me a hand , the formulas im tryin at moment are as follows:

=if(cell="",(cell=""),1,0)
=IF(I12=ISBLANK(J12=isblank),1,0)
thanks
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
You can use Conditional Formatting, under Format, Conditional Formatting...

I used two conditions, both should have "Formula Is" selected on the dropdown.
You'll need to hit the "Add >>" button to add in condition 2.
Condition 1: =AND(ISNUMBER($I$12),ISNUMBER($J$12),$I$12=0,$J$12=0)
Condition 2: =AND(ISBLANK($I$12),ISBLANK($J$12))

Hope that helps!
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
For the formatting red if blank, select the entire row (row 1 in my example)
Format -> conditional format. Select formula is and enter the following formula and select pattern red

=AND(ISBLANK($A1),ISBLANK($B1)).

Is it the same area that you want to format green if either of the two cells contains a zero?
 

mercmannick

Well-known Member
Joined
Jan 21, 2005
Messages
730
no bcos basically the two cols im lookin at are,

if text in cel ignore, if text in next cell ignore,
if fisrt cell is blank and second is not ignore,
only highlight if both are empty

second fomula needed is if col has a 0 then highlight all row green

hope this helps a bit

thnx
 

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848

ADVERTISEMENT

The solution to your problem is conditional formatting. Follow the steps below:

1) Select the cells for which you want to add conditional formatting
2) On the Format menu, click Conditional Formatting.
3) click Formula Is and then enter the formula =AND($B$1="",$C$1="")
4) Click Format
5) Select the formatting you want , i.e. red shade.

To add another condition, click Add, and then repeat steps 3 through 5, using the formula: =($B$1="0") and as format green shade.

Ciao
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi,

I don't think you can make it by the worksheet function.

Right click on the excel icon on the left side of the File menu.
select View Code then paste the code onto the right side and get back to the excel screen.

go to Tool -> Macro -> Macro choose ThisWorkbook.clr then OK

Code:
Sub clr()
Dim r As Range
With ActiveSheet
    .Cells.Interior.ColorIndex = xlNone
    For Each x In .Range("i1:i10")    'alter the range
        Select Case x.Value
            Case Is = ""
                If IsEmpty(x.Offset(, 1)) Then
                    x.EntireRow.Interior.ColorIndex = 3
                End If
            Case Is = "0"
                x.EntireRow.Interior.ColorIndex = 10
        End Select
    Next
End With
End Sub
hope this helps
jindon
P.S Yes! Conditional formatting will do.
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446

ADVERTISEMENT

mercmannick said:
:oops: :oops:
sorry but i am tryin every way i know how.........

i have a worksheet,
that i would like a formula to look at contents in one cell (all text by the way)............

Is the zero for condition 2 definately a text zero rather than a number zero?

Condition 1 should be working ?
 

mercmannick

Well-known Member
Joined
Jan 21, 2005
Messages
730
can i post a sample of worksheet so u have better idea of what i mean ?
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
Of course you can, use Colos HTML maker. Link is at bottom of the page.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,307
Messages
5,641,439
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top