Conditional Formatting in reverse ?

[Salubriouscreature]

Does anyone know if the following is possible?

I ahve a spreadsheet that I cut and paste data into from an html generated table. The table is coloured in either red or white. If it is red then I want to record this.

At the moment I cut and paste and then manually type "1" into each red box and then format paint some conditional formatting to make the background red and the text red. This then shows a red cell but allows me to use the "1" to calculate various calcs..etc.

My question is; How can I get excel to enter a 1 for me? Some form of macro etc....

Yours in anticipation....

 

[lhite]

Add a new column to your spreadsheet (you could even hide it if you wanted) and use basically the same criteria as you used for the conditional formatting.

For example:
=IF(a2<10,1,0)
would display a 1 if the value in a2 was less than 10, otherwise it would be zero. You could then sum that column.

-Larry

 

[Salubriouscreature]

Thanks for that.

The issue is a little more complex than that. Is there anyway I can post a gif on this forum? I could show you the format of the spreadsheet then.,...

Is there any other way?

 

[combo]

Try user defined function:

Function IsRed(rCell As Range) As Integer
If rCell.Interior.Color = vbRed Then
IsRed = 1
Else
IsRed = 0
End If
End Function

If the color is not pure red pick its RGB and use in code.
Note: If you chage cell colur function does not recalculate automatically. Use F9 to do tht.

 

[Salubriouscreature]

Thanks Combo this looks hopeful.

I am not familiar with the VB side of Excel. I seem to have managed to get the script listed in the custom formulas and have then tried to use it in a cell. What is the syntax of the formula for the entry into the cell? And how do I find out what the RGB code is for the colour as it is red derivitive?

Thanks

 

[combo]

1. Activate VB window (Tools>Macro>Visual Basic Editor).
2. Insert module to your project (Insert>Module) IN VB Editor window.
3. Check RGB code of required colour:
a) activate the new module (if not active and copy code:
Sub xxx()
MsgBox ActiveCell.Interior.Color
End Sub
b) select excel window, select a cell with coloured interior and run above macro (Tools>Macro>Macros...), select xxx and OK. Write down number displayed in dialog box.
4. Copy whole code of function onto module, replace vbRed in the code by returned number. If you want to return other numbers (but here only integer) change them in code.

Use your function on sheets as normal worksheet function, with argument of analyzed cell. Note:
1. This works only in the spreadsheet with code (change code to Public Funcion ... to extend visibility, but still will have to have open workbook with code),
2. F9 will not recalculate this function, use additional row in definition:

Function IsRed(rCell As Range) As Integer
Application.Volatile
If rCell.Interior.Color = vbRed Then
...

Hope you will go through that

 

[combo]

Or more universal function, comparing two cells:

Public Function CompareInt(Cell_1 As Range, Cell_2 as Range, Val_1 as Variant, Val_2 as Variant) As Variant
Application.Volatile
If Cell_1.Interior.Color = Cell_2.Interior.Color
CompareInt = Val_1
Else
CompareInt = Val_2
End If
End Function

which returns Val_1 if interiors have the same colour, if not - Val_2
For instance in cell A1 use =CompareInt(B1,C1,1,0)

 

[Salubriouscreature]

Thanks Combo.

Sorry for being thick but...how do I use this function?

If I want to work out if the cell's interior colour is 8421631 (in this case) what would I enter in say, cell A1?

I have put =ifred(a1) but that returns a #NAME? error?

Regards

 

[combo]

This message means that spreadsheet does not see the function uou use (here: ifred). This is due to name of te function, I used iSred, you iFred, whis is not defined.

I think that the CoppareInt function is better. You do not need to pick colour separately and can assign values you wish both when colours are the same and when are different.

 

[Salubriouscreature]

The compare function generates a compile error in line 1.

If I used the first option what would I type in the formula? I get &quot;That name is not valid&quot; messages when I type in =isred(a1) or =isred().

Thanks

 

[combo]

Strange, the macro was working on my XP. Try to change name of the function (for instance IfRed). Try to change rCell to something more exotic.
You can send send me your worksheet if you like (mkb@op.pl).