How to Create an Excel VBA Macro to Find and Highlight Called Bingo Numbers in WPS Spreadsheets
Question details
The user needs a WPS Spreadsheets VBA macro that reads a called Bingo number, locates the matching value on a master sheet, applies shading to the corresponding cell, and handles missing values without errors.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- During a Bingo session, the caller enters a Bingo column letter and a called number, and the worksheet should validate the entry and highlight the matching master-sheet cell.
- Observed behavior
- The macro should find and shade the matching cell when the value exists, and show a message or exit cleanly when the value cannot be found or the Bingo column-number pair is invalid.
Make sure your workbook is macro-enabled and that the Master sheet uses the expected layout, for example B, I, N, G, O headers in row 1 and the called numbers below each header. In WPS Spreadsheets, confirm that the Developer tab is visible or that you can open the Visual Basic Editor from Tools > Macro.
Create a VBA macro that finds the called number and shades the matching master-sheet cell
Use this solution when you want the called Bingo number to automatically locate and highlight the matching value on the Master sheet and safely handle missing values.
This macro reads the Bingo column from G1 and the called number from J1, validates that the column is correct for the number, uses Range.Find to search the matching Master sheet column, and only formats the cell when a match is returned.
Click the Developer tab and choose Visual Basic or Macros. If the Developer tab is not visible, enable it from the ribbon options or use Tools > Macro > Visual Basic Editor.
In the Visual Basic Editor, right-click the project in the Project pane, choose Insert > Module, and paste the following macro. Adjust the worksheet name, header row, G1, J1, and search range if needed. Sub HighlightBingoNumber() Dim ws As Worksheet Dim bingoCol As String Dim calledNumber As Variant Dim expectedCol As String Dim searchRange As Range Dim headerCell As Range Dim foundCell As Range Set ws = ThisWorkbook.Worksheets("Master") bingoCol = Trim(ws.Range("G1").Value) calledNumber = ws.Range("J1").Value If Not IsNumeric(calledNumber) Then MsgBox "Enter a valid called number in J1." Exit Sub End If Select Case CLng(calledNumber) Case 1 To 15: expectedCol = "B" Case 16 To 30: expectedCol = "I" Case 31 To 45: expectedCol = "N" Case 46 To 60: expectedCol = "G" Case 61 To 75: expectedCol = "O" Case Else MsgBox "Called number must be between 1 and 75." Exit Sub End Select If UCase(bingoCol) <> expectedCol Then MsgBox "Invalid Bingo column '" & bingoCol & "' for number " & calledNumber & ". Expected column " & expectedCol & "." Exit Sub End If Set headerCell = ws.Rows(1).Find(What:=bingoCol, LookIn:=xlValues, LookAt:=xlWhole) If headerCell Is Nothing Then MsgBox "Bingo column header not found on the Master sheet." Exit Sub End If Set searchRange = ws.Range(headerCell.Offset(1), ws.Cells(ws.Rows.Count, headerCell.Column).End(xlUp)) Set foundCell = searchRange.Find(What:=calledNumber, LookIn:=xlValues, LookAt:=xlWhole) If Not foundCell Is Nothing Then foundCell.Interior.Color = RGB(255, 255, 0) foundCell.Select Else MsgBox "Value not found in column " & bingoCol & "." End If End Sub
Replace "Master" with your actual sheet name, adjust the header row if it is not row 1, and confirm that G1 contains the Bingo column letter and J1 contains the called number.
Place the cursor inside the macro and press F5, or close the editor and run the macro from Developer > Macros > Run. Enter a valid Bingo column and number, then check that the matching cell is shaded and that missing values show the "Value not found" message.
Add a Bingo column formula and data validation to prevent invalid combinations
Use this solution when you want WPS Spreadsheets to calculate the correct Bingo column from the called number and block invalid pairs such as column N with number 46 before running the macro.
Use WPS Spreadsheets to automate Bingo number lookup and highlighting with VBA
WPS Spreadsheets supports Excel-compatible VBA macros, so you can enter a called Bingo number and automatically find, highlight, and validate the matching cell without switching to another spreadsheet application.
- 1. Download and install WPS Office: Get WPS Office from the official WPS website, install it, and open WPS Spreadsheets.
- 2. Open your macro-enabled workbook: Click File > Open and select the .xlsm workbook that contains the Bingo master sheet and macro.
- 3. Enable or run the macro: If prompted, enable macros. Use Developer > Macros > Run to execute the highlight macro, or open the Visual Basic Editor to edit the code.
- 4. Enter called Bingo numbers and verify the highlight: Type the Bingo column in G1 and the called number in J1, run the macro, and check that the matching master-sheet cell is shaded.
Frequently Asked Questions
Why does the macro show 'Value not found' even though the number appears on the Master sheet?
Check that the Bingo column letter in G1 matches the header row exactly, that the Master sheet range searched by the macro contains the number, and that the called number is stored as a number instead of text in J1.
How can I prevent invalid Bingo column-number pairs such as N and 46?
Use the formula =CHOOSE(CEILING.MATH(J1/15),"B","I","N","G","O") to calculate the correct column from the number, and add data validation to G1 and J1 so invalid pairs are blocked or flagged before the macro runs.
Does WPS Spreadsheets support the Excel Range.Find method in VBA?
Yes. WPS Spreadsheets supports Excel-compatible VBA macros, including Range.Find, worksheet references, and formatting commands. Save the workbook as a macro-enabled .xlsm file and allow macros if prompted.
How do I change the highlight color in the macro?
Edit the line foundCell.Interior.Color = RGB(255, 255, 0) in the Visual Basic Editor and replace the RGB values with the red, green, and blue values for your preferred color.
What should I do if the Developer tab is missing in WPS Spreadsheets?
Enable the Developer tab from the ribbon options, or open the Visual Basic Editor from Tools > Macro > Visual Basic Editor in the WPS Spreadsheets menu.
