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

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


--
EdMac
------------------------------------------------------------------------
EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736
View this thread: http://www.excelforum.com/showthread...hreadid=522556

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
Choose formula to be =OR(C4="Fail";C4="Wrong")
Choose the format to be red


nsv


--
nsv
------------------------------------------------------------------------
nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
View this thread: http://www.excelforum.com/showthread...hreadid=522556

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

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

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

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

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

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

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