Multiple Conditional Formating

[Mandeep Dhami]

Hi,

First Query:

I am preparing worksheet wherein I require 4 conditional formating, but only
3 are available.
Please could you help me have more then 3 conditional formating.

Second Query:

The formating goes by adding certain numbers/words in cell and the colour of
the cell changes. I want whatever we enter in cell should not be visible but
only the cell should get coloured as per colours assigned to that particular
number or word.

Cheers,
Mandeep

[EdMac]

Hi Mandeep,

As you say you are limited to 3 conditional formats but you also have the default format so you may be able to achieve what you want.

If you want just a coloured cell you have two options. One is to format the cell purely on the result of another cell or you can set the font colour within the cell to be the same as the cell colour.

HTH

Ed

[nsv]

I got some clever answers to the same question on march 8. around 1600 hrs.

You can have only 3 different outputs + the default, but if you choose fomula in stead of cell vaule it is possible to write a logical expression that gives you the same output for several different conditions.

Example - you want cell C4 to be red if the value is "Fail" or "Wrong":
Put the cursor in C4
Conditional formatting
Choose formula to be =OR(C4="Fail";C4="Wrong")
Choose the format to be red


nsv

[gjcase]

You also can specify a format for neagtive numbers, that can add one

---Glenn

[Mandeep Dhami]

Thanks to all for your reply.
I think I have not got what I required.

In my spreadsheet I want four conditional formatings.
If I enter:
Number 1 in any cell the colour should change to Bright Green,
Number 2 in any cell the colour should change to Yellow,
Number 3 in any cell the colour should change to Gold,
Number 4 in any cell the colour should change to Rose.

What I want is that whenever I enter any numbers in any cell in any sequence
the colour as per above should be filled in that particular cell.........but
the value entered should not be displayed......for eg: if I enter number 2,
the cell should change to colour Yellow but at the same time number 2 should
not be visible in that cell.

Cheers,
Mandeep



"gjcase" wrote:

>
> You also can specify a format for neagtive numbers, that can add one
>
> ---Glenn
>
>
> --
> gjcase
> ------------------------------------------------------------------------
> gjcase's Profile: http://www.excelforum.com/member.php...o&userid=26061
> View this thread: http://www.excelforum.com/showthread...hreadid=522556
>
>

[nsv]

In that case the conditional formatting can give you only three different colors. The fourth is the default ie. the color that applies to all other values in the cell.


If you want the figure itself to be invisible in the cell then choose the same color for the font as for the background

[Mandeep Dhami]

Thanks for the reply.

The suggestion that you gave is which I am aware of. I was expecting that
there should be some thing automation with some formula as there are many
cells with the conditional formating and if as per the suggestion I will
start changing the font colour to that of cell colour it will take hell lot
of time and which will not be worth.

Do let me know if there is solution in preparing macro for the same, it can
also have more then 3 conditionla formating also.

Cheers,
Mandeep


"nsv" wrote:

>
> In that case the conditional formatting can give you only three
> different colors. The fourth is the default ie. the color that applies
> to all other values in the cell.
>
>
> If you want the figure itself to be invisible in the cell then choose
> the same color for the font as for the background
>
>
> --
> nsv
> ------------------------------------------------------------------------
> nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
> View this thread: http://www.excelforum.com/showthread...hreadid=522556
>
>

[Worthy]

This might be too late, but if you still require multiple conditional
formatting, try the following function ...

Function RAG(StrtRow As Integer, EndRow As Integer, StrtCol As Integer,
EndCol As Integer)
Dim c, r

c = StrtCol
Do Until c > EndCol

r = StrtRow
Do Until r > EndRow

If Not IsNumeric(Cells(r, c).Value) Or Cells(r, c).Value = "" Then
Cells(r, c).Interior.ColorIndex = xlNone
Cells(r, c).Font.ColorIndex = 0
ElseIf IsNumeric(Cells(r, c).Value) Then
If Cells(r, c).Value = 1 Then
Cells(r, c).Interior.ColorIndex = 3
Cells(r, c).Font.ColorIndex = 3
ElseIf Cells(r, c).Value = 2 Then
Cells(r, c).Interior.ColorIndex = 19
Cells(r, c).Font.ColorIndex = 19
ElseIf Cells(r, c).Value = 3 Then
Cells(r, c).Interior.ColorIndex = 24
Cells(r, c).Font.ColorIndex = 24
ElseIf Cells(r, c).Value = 4 Then
Cells(r, c).Interior.ColorIndex = 35
Cells(r, c).Font.ColorIndex = 35
End If


End If
r = r + 1
Loop
c = c + 1
Loop


End Function


Hope this helps

--
Paul


"Mandeep Dhami" wrote:

> Thanks for the reply.
>
> The suggestion that you gave is which I am aware of. I was expecting that
> there should be some thing automation with some formula as there are many
> cells with the conditional formating and if as per the suggestion I will
> start changing the font colour to that of cell colour it will take hell lot
> of time and which will not be worth.
>
> Do let me know if there is solution in preparing macro for the same, it can
> also have more then 3 conditionla formating also.
>
> Cheers,
> Mandeep
>
>
> "nsv" wrote:
>
> >
> > In that case the conditional formatting can give you only three
> > different colors. The fourth is the default ie. the color that applies
> > to all other values in the cell.
> >
> >
> > If you want the figure itself to be invisible in the cell then choose
> > the same color for the font as for the background
> >
> >
> > --
> > nsv
> > ------------------------------------------------------------------------
> > nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
> > View this thread: http://www.excelforum.com/showthread...hreadid=522556
> >
> >

[Mandeep Dhami]

Thanks Worthy.........its never to late to learn new things.
I don't understand where to enter this macro.
If you could just send me the file with this macro it will be of gr8 help.
My mail id is [email protected]

Cheers,
Mandeep


"Worthy" wrote:

> This might be too late, but if you still require multiple conditional
> formatting, try the following function ...
>
> Function RAG(StrtRow As Integer, EndRow As Integer, StrtCol As Integer,
> EndCol As Integer)
> Dim c, r
>
> c = StrtCol
> Do Until c > EndCol
>
> r = StrtRow
> Do Until r > EndRow
>
> If Not IsNumeric(Cells(r, c).Value) Or Cells(r, c).Value = "" Then
> Cells(r, c).Interior.ColorIndex = xlNone
> Cells(r, c).Font.ColorIndex = 0
> ElseIf IsNumeric(Cells(r, c).Value) Then
> If Cells(r, c).Value = 1 Then
> Cells(r, c).Interior.ColorIndex = 3
> Cells(r, c).Font.ColorIndex = 3
> ElseIf Cells(r, c).Value = 2 Then
> Cells(r, c).Interior.ColorIndex = 19
> Cells(r, c).Font.ColorIndex = 19
> ElseIf Cells(r, c).Value = 3 Then
> Cells(r, c).Interior.ColorIndex = 24
> Cells(r, c).Font.ColorIndex = 24
> ElseIf Cells(r, c).Value = 4 Then
> Cells(r, c).Interior.ColorIndex = 35
> Cells(r, c).Font.ColorIndex = 35
> End If
>
>
> End If
> r = r + 1
> Loop
> c = c + 1
> Loop
>
>
> End Function
>
>
> Hope this helps
>
> --
> Paul
>
>
> "Mandeep Dhami" wrote:
>
> > Thanks for the reply.
> >
> > The suggestion that you gave is which I am aware of. I was expecting that
> > there should be some thing automation with some formula as there are many
> > cells with the conditional formating and if as per the suggestion I will
> > start changing the font colour to that of cell colour it will take hell lot
> > of time and which will not be worth.
> >
> > Do let me know if there is solution in preparing macro for the same, it can
> > also have more then 3 conditionla formating also.
> >
> > Cheers,
> > Mandeep
> >
> >
> > "nsv" wrote:
> >
> > >
> > > In that case the conditional formatting can give you only three
> > > different colors. The fourth is the default ie. the color that applies
> > > to all other values in the cell.
> > >
> > >
> > > If you want the figure itself to be invisible in the cell then choose
> > > the same color for the font as for the background
> > >
> > >
> > > --
> > > nsv
> > > ------------------------------------------------------------------------
> > > nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
> > > View this thread: http://www.excelforum.com/showthread...hreadid=522556
> > >
> > >