Last active
August 7, 2024 07:13
-
-
Save giobel/c4e992607831cb02d6cd21f9d7e217f7 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Excel Scripts |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| function main(workbook: ExcelScript.Workbook) { | |
| let selectedSheet = workbook.getActiveWorksheet(); | |
| let rgbRange = selectedSheet.getRange("A1:A6") | |
| let rangeValues = rgbRange.getValues(); | |
| let rangeLength = rangeValues.length; | |
| for (let i=0; i < rangeLength; i++) { | |
| let color = rangeValues[i][0].toString(); | |
| let rgbParts = color.split(' ') | |
| const [r, g, b] = rgbParts; | |
| let targetCell = selectedSheet.getCell(i,0); | |
| let hexcolor = rgbToHex(Number(r), Number(g),Number(b)); | |
| targetCell.getFormat().getFill().setColor(hexcolor); | |
| } | |
| } | |
| function rgbToHex(r:number, g:number, b:number) { | |
| return '#' + ((1 << 24) + (r << 16) + (g << 8) + b).toString(16).slice(1).toUpperCase(); | |
| } |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Sub ColorCells() | |
| Dim ws As Worksheet | |
| Dim rgbRange As Range | |
| Dim rangeValues As Variant | |
| Dim i As Long | |
| Dim rgbParts As Variant | |
| Dim r As Integer, g As Integer, b As Integer | |
| Dim targetCell As Range | |
| Dim colorRow As Variant | |
| colorRow = Array("E", "G", "I", "K", "M") | |
| endRow = 250 | |
| ' Set the active worksheet | |
| Set ws = ThisWorkbook.ActiveSheet | |
| ' Define the range of RGB values | |
| ' Set rgbRange = ws.Range("E2 :E222") | |
| For j = LBound(colorRow) To UBound(colorRow) | |
| Set rgbRange = ws.Range(Cells(2, colorRow(j)), Cells(endRow, colorRow(j))) | |
| ' Get the values from the RGB range | |
| rangeValues = rgbRange.Value | |
| ' Loop through the RGB values | |
| For i = 1 To UBound(rangeValues, 1) ' VBA arrays are 1-based | |
| ' Get the RGB value from the cell | |
| Dim color As String | |
| color = CStr(rangeValues(i, 1)) | |
| ' Split the RGB value into its parts | |
| rgbParts = Split(color, " ") | |
| If UBound(rgbParts) = 2 Then ' Ensure there are exactly 3 parts | |
| r = CInt(rgbParts(0)) | |
| g = CInt(rgbParts(1)) | |
| b = CInt(rgbParts(2)) | |
| ' Define the target cell in the same row | |
| Set targetCell = ws.Cells(i + 1, colorRow(j)) | |
| ' Set the background color using RGB | |
| targetCell.Interior.color = RGB(r, g, b) | |
| End If | |
| Next i | |
| Next j | |
| End Sub |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment