How to make a more interactive spreadsheet?

[Guest]

Hello all. So here are 2 things I am trying to do.

I would like to make a worksheet function or process, that will change an
entire row green when 2 values in that row match. For instance K1 cell value
is 1 and R1 cell value is 1, the line would turn green, if it was a 0 in R1
then nothing would happen. Heres the catch though, I want that to happen in
real time. So when I type 1 and hit enter, the line would turn green.

Heres the second thing I want to do!
I would like to make some check boxes, that when checked will turn a line
red (or another color). I would also like to do this for several lines, maybe
200, once the macro is made for that, is there a way to attach it to its
specific cell, without having to retype a macro 200 times?

Thanks in advance

 

[Jim Rech]

Select all of row 1 then open the Format, Conditional Formatting dialog.
Change the "Cell value is" to "Formula is" and paste in this formula:

=MAX(COUNTIF(1:1,1:1))>1

and then set the format you want when this formula is true.

--
Jim Rech
Excel MVP
| Hello all. So here are 2 things I am trying to do.
|
| I would like to make a worksheet function or process, that will change an
| entire row green when 2 values in that row match. For instance K1 cell
value
| is 1 and R1 cell value is 1, the line would turn green, if it was a 0 in
R1
| then nothing would happen. Heres the catch though, I want that to happen
in
| real time. So when I type 1 and hit enter, the line would turn green.
|
| Heres the second thing I want to do!
| I would like to make some check boxes, that when checked will turn a line
| red (or another color). I would also like to do this for several lines,
maybe
| 200, once the macro is made for that, is there a way to attach it to its
| specific cell, without having to retype a macro 200 times?
|
| Thanks in advance

 

[Tom Ogilvy]

Use checkboxes from the Forms toolbar. Assign them all to a single macro
similar to

Sub cbox_click()
Dim cbox as Checkbox
Dim rng as Range
set cbox = ActiveSheet.Checkboxes(application.Caller)
set rng = cbox.TopLeftCell
if cbox.Value = xlOn then
cbox.EntireRow.Interior.ColorIndex = 5
else
cbox.EntireRow.Interior.ColorIndex = xlNone
end if
End Sub

 

[Guest]

Jim, thanks that helps alot. Im assuming that on row 7 for instance i would
change the 1:1,1:1 to 7:7,7:7? Unfortuneatly i need to different cells to
meet that criteria. How would i do if if I only wanted that to apply to N7
and T7?

Tom, almost got this to work, however this line

cbox.EntireRow.Interior.ColorIndex = 5

Keeps comming up as invalid context. also you dont need this line

cbox.EntireRow.Interior.ColorIndex = xlNone

You could just do the Else, then End if =)

 

[Guest]

Gotta throw a bump up in here =) need to get this info =)

 

[Tom Ogilvy]

Sub cbox_click()
Dim cbox As CheckBox
Dim rng As Range
Set cbox = ActiveSheet.CheckBoxes(Application.Caller)
Set rng = cbox.TopLeftCell
If cbox.Value = xlOn Then
rng.EntireRow.Interior.ColorIndex = 5
Else
rng.EntireRow.Interior.ColorIndex = xlNone
End If
End Sub

Worked fine for me. If you never want the line not be highlighted, I guess
you can remove the xlNone.