Cell Ranges with an IF statement

[pells]

I have the following code code so that if something is entered in cell range G11 then H11 equals "9".

If ActiveSheet.Range("G11") > 0 Then ActiveSheet.Range("H11") = "9"

This works fine, but I want to extend the code so that the range is from G11 to G88, so if anything is entered in any of the cells in the range G11 to G88, then the corresponding cell H, should have a "9" entered in it.

If I change the code to:

If ActiveSheet.Range("G11:G88") > 0 Then ActiveSheet.Range("H11:H88") = "9" a "9" appears in all the cells from H11 to H11, which is wrong.

Please can anyone help me with what I am trying to achieve?

Many thanks.

 

[Sektor]

Put it into worksheet module:

[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] Worksheet_Change([COLOR="Blue"]ByVal[/COLOR] Target [COLOR="Blue"]As[/COLOR] Range)
    [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] Intersect(Target, Range("G11:G88")) [COLOR="Blue"]Is[/COLOR] [COLOR="Blue"]Nothing[/COLOR] [COLOR="Blue"]Then[/COLOR]
        Cells(Target.Row, "H") = 9
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[pells]

Put it into worksheet module:

[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] Worksheet_Change([COLOR="Blue"]ByVal[/COLOR] Target [COLOR="Blue"]As[/COLOR] Range)
    [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] Intersect(Target, Range("G11:G88")) [COLOR="Blue"]Is[/COLOR] [COLOR="Blue"]Nothing[/COLOR] [COLOR="Blue"]Then[/COLOR]
        Cells(Target.Row, "H") = 9
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

Many thanks for your reply sektor. I have tried this but it doesnt seem to work. I already have a Worksheet_Change private sub to put all change all text to upper case. My code is as follows and you maybe able to see where I am going wrong:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Target = UCase(Target)

If Not Intersect(Target, Range("G11:G88")) Is Nothing Then
Cells(Target.Row, "H") = "9"
End If

Application.EnableEvents = True
On Error GoTo 0
End Sub

Many thanks.

 

[Sektor]

Try this

[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] Worksheet_Change([COLOR="Blue"]ByVal[/COLOR] Target [COLOR="Blue"]As[/COLOR] Range)
    
    Application.EnableEvents = [COLOR="Blue"]False[/COLOR]
    
    [COLOR="Blue"]If[/COLOR] Target.Count > 2 [COLOR="Blue"]Or[/COLOR] Target.HasFormula [COLOR="Blue"]Then[/COLOR] [COLOR="Blue"]Exit[/COLOR] [COLOR="Blue"]Sub[/COLOR]
    Target = UCase(Target)
    
    [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] Intersect(Target, Range("G11:G88")) [COLOR="Blue"]Is[/COLOR] [COLOR="Blue"]Nothing[/COLOR] [COLOR="Blue"]Then[/COLOR]
        Cells(Target.Row, "H") = "9"
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]

    Application.EnableEvents = [COLOR="Blue"]True[/COLOR]

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[pells]

Try this

[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] Worksheet_Change([COLOR="Blue"]ByVal[/COLOR] Target [COLOR="Blue"]As[/COLOR] Range)
    
    Application.EnableEvents = [COLOR="Blue"]False[/COLOR]
    
    [COLOR="Blue"]If[/COLOR] Target.Count > 2 [COLOR="Blue"]Or[/COLOR] Target.HasFormula [COLOR="Blue"]Then[/COLOR] [COLOR="Blue"]Exit[/COLOR] [COLOR="Blue"]Sub[/COLOR]
    Target = UCase(Target)
    
    [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] Intersect(Target, Range("G11:G88")) [COLOR="Blue"]Is[/COLOR] [COLOR="Blue"]Nothing[/COLOR] [COLOR="Blue"]Then[/COLOR]
        Cells(Target.Row, "H") = "9"
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]

    Application.EnableEvents = [COLOR="Blue"]True[/COLOR]

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

Many thanks Sektor, but unfortuantely his doesn't work either. The uppercase does, but putting in the "9" cell H doesn't.

Any other ideas?

Many thanks for all your help.

 

[Sektor]

According to your request, you want 9 in H column when you enter something into G11:G88 range. The code does this. Do you enter data into correct range?

 

[pells]

According to your request, you want 9 in H column when you enter something into G11:G88 range. The code does this. Do you enter data into correct range?

That is correct, but it unfortuntly doesn't work

I am entering data, which is numerical, into the correct range.

 

[Sektor]

Try to remove

If Target.Count > 2 Or Target.HasFormula Then Exit Sub

 

[pells]

Try to remove

If Target.Count > 2 Or Target.HasFormula Then Exit Sub

Thanks sektor - I think I know what the problem is! Cell range G11:G88 is a calculated field. If I manually enter is a value in a cell within this range, a 9 is entered in the corresponding cell H.

Is there a way of getting this to work based on a calculated cell?

Once again, many thanks for all your help, you have been great!

 

[Sektor]

What is calculated field? You mean PivotTable?