Row watch and single cell change

BMD

Board Regular
Joined
Oct 5, 2005
Messages
211
All,
I did a search for 'Rows' and tryed to modify what I found.
This needs to watch row 7 and 15 and 16 and 23, if it finds a '%100' smiple make it green, but only that cell and not the whole row and the %100 is a formula and not entered.

Bruce.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
Bruce

Do you have any code?

Is it individual cells in the rows you want to highlight?

How will the values in the cells get changed? Are the formula based on input in other cells?
 

BMD

Board Regular
Joined
Oct 5, 2005
Messages
211
Norie,
I'm home right now so I don't have the code.
But the values are changed by formulas based on many cell on different sheets all in one workbook.
Can It Be Done,
Bruce.
 

BMD

Board Regular
Joined
Oct 5, 2005
Messages
211
I just tryed to rename the Column to Rows from the other function that changes the whole row based on two columns.

Bruce.
I'm going to mess around with this now.
Thanks.

Code:
Public Sub Worksheet_SelectionChange(ByVal SS As Object, ByVal Target)
Dim WatchRangeA
Dim WatchRangeB
Dim WatchRangeC
Dim WatchRangeD
'Dim cell As Range
'Dim rng As Range
Dim myMultiAreaRange As Range

Set WatchRangeA = SS.Row8
Set WatchRangeB = SS.Rows15
Set WatchRangeC = SS.Rows16
Set WatchRangeD = SS.Rows23

Set myMultiAreaRange = Union(WatchRangeA, WatchRangeB, WatchRangeC, WatchRangeD)

If Intersect(Target, myMultiAreaRange) Is Nothing Then Exit Sub
    
'Set rng = Intersect(Target, myMultiAreaRange)

'For Each cell In rng

'    With Sh.Range(Sh.Cells(cell.Row, "A"), Sh.Cells(cell.Row, "O")).Font
'        Select Case LCase(cell):

    If WatchRangeA = 1 Then
        .ColorIndex = 33
        .Bold = True
    End If
End If

'End Sub


End Sub
 

BMD

Board Regular
Joined
Oct 5, 2005
Messages
211

ADVERTISEMENT

I have:
Code:
Private Sub Worksheet_Change(ByVal Sh As Object, ByVal Target As Range)
Dim WatchRange As Range
Dim WatchRangeM As Range
Dim cell As Range
Dim rng As Range
Dim myMultiAreaRange As Range

Set WatchRange = Sh.Rows("8")
Set WatchRangeI = Sh.Rows("15")

Set myMultiAreaRange = Union(WatchRange, WatchRangeI)

If Intersect(Target, myMultiAreaRange) Is Nothing Then Exit Sub
    
Set rng = Intersect(Target, myMultiAreaRange)

For Each cell In rng

    With Sh.Range(Sh.Cells(cell)).Font
        If Sh.Range = 1 Then
          .Bold = False
        End If
    End With
Next cell
End Sub
I get an odd message that the "procdure declaration does not match description of event of procedure having the same name?
 

BMD

Board Regular
Joined
Oct 5, 2005
Messages
211
This works but I need it to watch a range B8:Q8 but:
If Target.Address = "(B8:Q8)" Then
If Target.Range = "(B8:Q8)" Then
Does not Work.

Code:
Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$B$8" Then
    If Target.Value = 1 Then
        Target.Font.Bold = True
    Else
        Target.Font.Bold = False
    End If
End If
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try this.
Code:
Sub Worksheet_Change(ByVal Target As Excel.Range) 
If Intersect(Range("B8:Q8"),  Target( Is Nothing Then Exit Sub
   Target.Font.Bold = (Target.Value = 1) 
End Sub
 

BMD

Board Regular
Joined
Oct 5, 2005
Messages
211
Norie,
This is what I have now
Code:
Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$B$8" Then
    If Target.Value > 0.01 And Target.Value < 0.25 Then
        Target.Font.Bold = True
        Target.Font.ColorIndex = 3
    Else
        Target.Font.Bold = False
        Target.Font.ColorIndex = 1
    End If
End If
End Sub
Will try to intergrate yor to it.



PS and Off Topic.
What part of Nauru are you from. I did a little reading on your place, not very big but sounds peaceful. I’m embarrassed to say I did not know were it was so I had to find it.

Thanks,
Bruce.
 

BMD

Board Regular
Joined
Oct 5, 2005
Messages
211
It works but does not auto update is the value in the formula changes. If I click on the cell and then enter the function runs then but not as teh value changes.
Code:
Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("B7:Q7"), Target) Is Nothing Then Exit Sub
    If Target.Value > 0.01 And Target.Value < 0.25 Then
        Target.Font.Bold = True '(Target.Value = 1)
        Target.Font.ColorIndex = 3
    Else
        Target.Font.Bold = False '(Target.Value = 1)
        Target.Font.ColorIndex = 1
    End If
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
That's why I asked if it was a formula.

The Change event is not triggered by a the result of a formula changing.

Do you really need to do this with code? Couldn't you just use Conditional Formatting?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,035
Messages
5,569,784
Members
412,292
Latest member
The Bear named Joe
Top