Excel - Replacing specific color of whole sheet? Topic is solved

[Scr1pter]

Hello guys,

Do you think it's possible to use COM in order to replace a specific color within a worksheet?
I work with an Excel file which contains profiles.
To have a good visual feedback/overview, I use different colors for the sheets/profiles.

When I need to create a new profile, I usually copy an existing one.
The problem:
I always have to change the colors manually which is pretty effortful.

To give you an overview of how I mostly use colors, I created this example worksheet.


Do you believe there is a way that COM searches for color RED and replaces it by a specified color?
E.g. User presses F1, chooses a color and all red elements get replaced by the chosen color.

The alternative version would be a loop which sets the color of all specific elements
and a border color of a range of cells + the worksheet color.

Thanks for any help!

Regards

[Scr1pter]

Hello guys,

I was able to make some progress.
With my current code I'm able to change the border color and text color of a range of cells.
Even though it works, it doesn't change borders of several cells.
For the text color it's not that dramatical, but it's important for me to change the border colors.
Any idea how to achieve that?
This is my current code which works partly (see gif animation):

Code: Select all

F7::
xl := ComObjActive("Excel.Application") ; Connect to Excel instance
ws := xl.ActiveSheet ; Connect to current worksheet
column = 65 ; Number 65 = String A
Loop, 1040 ; (40 rows x 26 columns)
{
  row++ ; Start at row 1
  if (row = 41) ; If Row 41 has been reached:
  {
    row = 1 ; Set row back to 1
    column := column + 1 ; Jump to next column
  }
  char := Chr(column) ; Convert number to String
  cell = %char%%row% ; Determinate cell, e.g. A1, B20, C3 etc.
  if xl.range(cell).Borders.Color = 0x0000FF ; If border color of current cell is red:
  {
    xl.range(cell).Borders.Color := 0xFFD700 ; Change it to blue
    xl.range(cell).Borders.Weight := 3 ; Set weight of borders to medium - Probably not needed cause it's already set
  }
  if xl.range(cell).Font.Color = 0x0000FF ; If font color of current cell is red:
  {
    xl.range(cell).Font.Color := 0xFFD700 ; Change it to blue
  }
}
return

For the shape colors I already gave up - no chance.
Tried anything like:

Code: Select all

ActiveChart.SeriesCollection(1).MarkerForegroundColor := 3

OR

for myShape in ws.shapes
{
  myShape.GridlineColor := 3
  ;Textbox1.Fill.ForeColor := 0x0000FF
} 

OR

Shape1.ForeColor := 2

I read there's a bug in Excel 2007 (which I use) regarding the shape colors.

This is how it looks in real:


Thanks for any help!

Regards

[Scr1pter]

Forum bug solved?

[FanaticGuru]

Below is an example that shows more easily how to loop through all of the used cells in an Excel sheet.

Code: Select all

#F8::
  xlApp := ComObjActive("Excel.Application") 
  xlApp.ScreenUpdating := false
  for xlCell in xlApp.ActiveSheet.UsedRange.Cells
  {
    for xlBorder in xlCell.Borders
      if (xlBorder.Color =  0x0000FF)
      {
        xlBorder.Color := 0xFFD700
        xlBorder.Weight := 3
      }
    if (xlCell.Font.Color = 0x0000FF)
      xlCell.Font.Color := 0xFFD700
  }
  xlApp.ScreenUpdating := true
return

You also have to loop through 4 borders of each cell because Borders only works if all four borders are the same.

Which means this is going to be a lot of looping and is going to be relatively slow on a large sheet. Tried turning off ScreenUpdating but didn't seem to make that much difference.

FG

[FanaticGuru]

Here is an example to turn all red lines bluish.

Code: Select all

#F7::
  xlApp := ComObjActive("Excel.Application") 
  for xlShape in xlApp.ActiveSheet.Shapes
    if (xlShape.Line.ForeColor.RGB = 0x0000FF)
      xlShape.Line.ForeColor.RGB := 0xFFD700
return

This should be relatively quick as there are typically not many shapes to loop though in a sheet.

FG

[Scr1pter]

World-class!

I've mixed your scripts together and added some other things.
The script now checks the worksheet tab color and applies it to all font colors + border colors and shape colors.
Additionally it selects the next worksheet and repeats the operation until the sheet "END" has been reached.
(Probably there's another way to find out the last worksheet, anyway, this way it works too).

That's the code:

Code: Select all

Pause::
Pause
return

^F1:: ; Ctrl+F1
xl := ComObjActive("Excel.Application") ; Connect to current Excel instance
Loop
{
  ws := xl.ActiveSheet ; Connect to current worksheet
  na := xl.ActiveSheet.Name ; Name of current worksheet
  color := ws.tab.color ; Worksheet tab color  
  if (na = "END") ; If worksheet name is END:
  {
    break ; Leave loop
  }
  for xlCell in xl.ActiveSheet.UsedRange.Cells ; Check all used cells
  {
    for xlBorder in xlCell.Borders ; Check all borders
    {
      if (xlBorder.Color = 0x0000FF) ; If border color is red:
      {
        xlBorder.Color := color ; Change border color to worksheet tab color
      }
      if (xlCell.Font.Color = 0x0000FF) ; If font color is red:
      {
        xlCell.Font.Color := color ; Change font color to worksheet tab color
      }
    } 
  }
  for xlShape in xl.ActiveSheet.Shapes ; Check all shapes
  {
    if (xlShape.Line.ForeColor.RGB = 0x0000FF) ; If shape color is red:
    {
      xlShape.Line.ForeColor.RGB := color ; Change shape color to worksheet tab color
    }
  }
  ws.next.select ; Switch to next worksheet
  Sleep, 5 ; Give user a chance to pause the script if needed
}
return

Thank you very much, FanaticGuru!

Regards

[YoucefHam]

watch this List it can help you.

https://www.youtube.com/watch?v=UGOs1Z0 ... w6&index=6

[FanaticGuru]

World-class!

I've mixed your scripts together and added some other things.
The script now checks the worksheet tab color and applies it to all font colors + border colors and shape colors.
Additionally it selects the next worksheet and repeats the operation until the sheet "END" has been reached.
(Probably there's another way to find out the last worksheet, anyway, this way it works too).

You can loop through all sheets with a for statement.

Code: Select all

Pause::
Pause
return

^F1:: ; Ctrl+F1
xlApp := ComObjActive("Excel.Application") ; Connect to current Excel instance
for xlSheet in xlApp.Sheets ; Loop through all Sheets
{
  Color := xlSheet.Tab.Color ; Worksheet tab color  
  for xlCell in xlSheet.UsedRange.Cells ; Check all used cells
  {
    for xlBorder in xlCell.Borders ; Check all borders
    {
      if (xlBorder.Color = 0x0000FF) ; If border color is red:
      {
        xlBorder.Color := Color ; Change border color to worksheet tab color
      }
    } 
    if (xlCell.Font.Color = 0x0000FF) ; If font color is red:
    {
	  xlCell.Font.Color := Color ; Change font color to worksheet tab color
    }
  }
  for xlShape in xl.ActiveSheet.Shapes ; Check all shapes
  {
    if (xlShape.Line.ForeColor.RGB = 0x0000FF) ; If shape color is red:
    {
      xlShape.Line.ForeColor.RGB := Color ; Change shape color to worksheet tab color
    }
  }
}
return

Also you had the Font check inside the Border loop so the Font was being checked 4 times a cell which is not needed.

FG