conditional format 2010 return the cell reference not value, if 2 cells same val

[henksss]

Hi my name is Henk and I'm in Melbourne. I was wondering now that I have Office 2010 whether I can do a conditional format to compare a range of cells that have all different values in them to a row of 15 cells. Range being C14:G150 and Row being M4:X4. If a cell in the range is the same number as in the row, then change the value in the range to the Cell REFERENCE of the Row. I would only need to apply this rule once as once it has been done the range would be complete with cell references. Once I have the cell references in the range i can change the numbers in the row and the cell references will automatically change them in the range.


I used to have a code in VB in excell 2003 but was hoping to do it simply in conditional format option 2010 as there you can add more then 3 conditions.


Hope someone can help with a formula I can simply add into conditional farmatting tab.


Regards Henk

 

[SirJB7]

Hi, kenksss!


Reading more carefully your question, I'm afraid that it isn't possible to do that with conditional formatting, as CF only formats (font, style, color, background, etc.) and doesn't change values.


To achieve that you'll still need a macro, either manually run or triggered by a worksheet change event code.


Regards!

 

[henksss]

Can conditional format in excel 2010 return a cell reference like "A1" instead of a cell value if a criteria is met, if so what would be the formula?


Say cell A1 value is 5 and cell A3 is also 5 can A3 have a conditional format formula that will change the A3 value of 5 to reference "A1" instead of 5 value? If A3 was changed to "A1" the the result would still show the value of A1 being 5 but as I change the value of A1 from 5 to say 6 the value will change with it to 6 in A3.


It would be easy enough to manually put formula in A3 like "A1" but I have a range of 10,000 cells that would need to be done manually and would take too long when a simple formula could do this in "conditional format"


Regards Henk


This might be easier to understand then the first description.

 

[henksss]

Thanks SirJB7 thats a shame that cf can't do it. Could you help me with the VB code i could enter in VB? The code for VB that I used in excell 2003 was as follows.


Private Sub Worksheet_Deactivate()

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("m3")) Is Nothing Then Exit Sub

If Target = "" Then Exit Sub

Dim c As Range

With Application

.EnableEvents = False

.ScreenUpdating = False

For Each c In Range("c14:g150")

If c.Value = Target.Value Then c.Formula = "=$m$3"

Next c

.EnableEvents = True

.ScreenUpdating = True

End With

End Sub


I wonder if this same formula could work in excell 2010?


Regards Henk

 

[henksss]

to SIRJB7, YOU WROTE,


"Reading more carefully your question, I'm afraid that it isn't possible to do that with conditional formatting, as CF only formats (font, style, color, background, etc.) and doesn't change values."


Thanks for that info it helped me to stop looking for cf formula and I tried the vb above which I had for excell 2003 and learned that it works fine. So my problem is solved and thank you for the kind support.


Regards Henk

 

[SirJB7]

Hi, henksss!


Here's the updated code, and it works on 2010 version.

[pre]

-----
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("m3")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
Dim c As Range
With Application
.EnableEvents = False
.ScreenUpdating = False
For Each c In Range("c14:g150")
If c.Value = Target.Value Then c.Formula = "=$m$3"
Next c
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
-----

[/pre]

I deleted the first line since it wasn't necessary as there wasn't any code for the deactivate event.


Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.


Regards!