someone plz help , :)

[mercmannick]

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

 

[Tazguy37]

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]

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]

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]

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]

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

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]

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]

number 0

 

[mercmannick]

can i post a sample of worksheet so u have better idea of what i mean ?

 

[GorD]

Of course you can, use Colos HTML maker. Link is at bottom of the page.