I got error message While Writing Macro Code For Highlight The Color Red , If the Data Value is >= 120 And <=140 Using For ..Next Loop Can you Find The Error in My Macro Code, Where I Made Mistake?

[sv.thiyagarajan]

Hi Friends, I got error message While Writing Macro Code For Highlight The Color Red , If the Testing the Products ,Data Value is >= 120 And <=140 that is From 120 To 140 Using For …Next Loop, Can you Find The Error in My Macro Code, Where I Made Mistake ?

That is I want To High Light The Cell in Red Color which Values are Between 120 To 140
Here is The Code…

Sub Highlight_TheCell_UsingArray()

      Dim Doc As Object, Sheet As Object, Range As Object, Cell As Object, Cursor As Object
      Dim i As Integer, j As Integer, Data() As Variant
		
		Doc = ThisComponent : Sheet = Doc.Sheets.getByName("Sheet1") 
		Cell = Sheet.getCellRangeByName("A1")
		Cursor = Sheet.CreateCursorByRange(Cell)
		' Cursor.collapseToCurrentRegion()              
		Cursor.gotoEndOfUsedArea(True)
		Doc.CurrentController.Select(Cursor)
		
		Range = Cursor.getRangeAddress()
		Print Range.StartColumn & "," & Range.StartRow & "," & Range.EndColumn & "," & Range.EndRow 
		
		Data = Cursor.getDataArray()  ' Range(0, 0, 4, 10)
		
		For i = LBound(Data()) To UBound(Data())
			For j = LBound(Data(0)) To UBound(Data(0))
					If Data(i)(j) >= 120 And Data(i, j) <= 140 Then 
						Cell.CellBackColor = RGB(255,0,0)
					End If
			Next j 	
		Next i 			

End Sub

Here, Is the Calc Data File
Test_101.ods (9.1 KB)

[Villeroy]

Conditional formatting works.
Test_102.ods (17.9 KB)

[sv.thiyagarajan]

Hi Vileroy, I want to Write it In LibreOffice Macro Code…Here I got 90% success… but still got error…I don’t know where i made mistake…

[Villeroy]

The code works for me without errors. It changes the back color of cell A1 multiple times according to the values in the used area.

Cell = Sheet.getCellRangeByName("A1")
...
Cell.CellBackColor = RGB(255,0,0)

[KamilLanda]

Set proper Cell and no A1 repeatedly

If Data(i)(j) >= 120 And Data(i, j) <= 140 Then
	Cell=Sheet.getCellByPosition(x, y) 'proper Cell
	Cell.CellBackColor = RGB(255,0,0)
End If

[sv.thiyagarajan]

Hi Vileroy , Yes, Me also get the Cell.A1 got …RED COLOR. But, I want the Result, which is in Below Screen Shot. Like This… How to get below Result in Macro Code… I know i made little bit Mistake … But, I could not got that Complete Solution through Macro Code.

[JohnSUN]

…and to clarify a little more, then this fragment is better written as

					If (Data(i)(j) >= 120) And (Data(i)(j) <= 140) Then 
						Sheet.getCellByPosition(j,i).CellBackColor = RGB(255,0,0)
					End If

[sv.thiyagarajan]

Thanks, KamilLanda… To Point Out My Mistake.

[sv.thiyagarajan]

Thanks, JohnSUN… You Simplifying and fragmented the Macro Code…

[sv.thiyagarajan]

Thanks To KamilLanda and JohnSUN… Finally, It Works… and After, getting Correct Portion Of Macro Code… and Here, is the Code For Others who learn or getting Knowledge… Purpose…

Sub Highlight_TheCell_UsingArray

Dim Doc As Object, Sheet As Object, Range As Object, Cell As Object, Cursor As Object
Dim i As Integer, j As Integer, Data() As Variant
		
Doc = ThisComponent : Sheet = Doc.Sheets.getByName("Sheet1") 

		Cursor = Sheet.CreateCursor()            
		Cursor.gotoEndOfUsedArea(True)
		Doc.CurrentController.Select(Cursor)
		
		Range = Cursor.getRangeAddress()
		Print Range.StartColumn & "," & Range.StartRow & "," & Range.EndColumn & "," & Range.EndRow 
		
		Data = Cursor.getDataArray()        ' Range(0, 0, 4, 10)
		
		For i = LBound(Data()) +2 To UBound(Data())      ' i = 2 To 10
				For j = LBound(Data(0)) To UBound(Data(0))
					If (Data(i)(j)	>= 120) And (Data(i, j) <= 140) Then 	
						Sheet.getCellByPosition(j,i).CellBackColor = RGB(255,0,0)
					End If
			    Next j 	
		Next i 			

End Sub

Again Say Thanks and Credits goes to ,To KamilLanda & JohnSUN

‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
Here, are the Screen Shots and Calc Document for Practice

Before, Running Macro

‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
After, Running Macro in Calc Document and The Result

‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
Calc Document File

Test_101.ods (9.1 KB)